IF AND Formula help

[alp9423]

My formula is not working... IF both C6 & C7 are TRUE (meaning B6 & B7 contain text "y") then I want the formula to pull in the number from cell H7 in the Water Rate Sheet. How can I fix the formula to work properly? Thanks for the help!

=IF($C$6=TRUE,C11*'Water Rate Sheet'!F7,IF($C$7=TRUE,C11*'Water Rate Sheet'!G7,IF(AND(C6=TRUE,C7=TRUE),C11*'Water Rate Sheet'!H7,C11*'Water Rate Sheet'!E7)))

The other version I've tried is:

=IF($C$6=TRUE,C11*'Water Rate Sheet'!F7,IF($C$7=TRUE,C11*'Water Rate Sheet'!G7,IF(AND(B6="y",B7="y"),C11*'Water Rate Sheet'!H7,C11*'Water Rate Sheet'!E7)))

[#2]

@alp9423 

In your screenshot, C6 and C7 are empty.

Apart from that, you should test for both B6 and B7 being "y" before the individual tests.

Or:

=C11*IF($B$6="y", IF($B$7="y", 'Water Rate Sheet'!H7, 'Water Rate Sheet'!F7), IF($B$7="y", 'Water Rate Sheet'!G7, 'Water Rate Sheet'!E7))

[#3]

@Hans Vogelaar 

Thank you for the suggestion!

Yes, they work individually but not when I combine them in the formula, so my syntax must be wrong and it's just not obvious to me what is the error:

=IF($B$6="y",C11*'Water Rate Sheet'!F7,
IF($B$7="y",C11*'Water Rate Sheet'!G7,
IF($B$6="y",IF($B$7="y",C11*'Water Rate Sheet'!H7,
C11*'Water Rate Sheet'!E7))))

[#4]

@alp9423 

The problem with your formula is that if B6 equals "y", the first condition is true, so the formula returns C11*'Water Rate Sheet'!F7 and ignores the rest, so the part IF($B$6="y",IF($B$7="y" is not evaluated.

The formula that I posted takes care of that. (Please note that it is a single formula)

[#5]

@Hans Vogelaar

Brilliant!! It works just like you said. I was definitely over complicating things.
100 Thank You's! :)