Forum Discussion

alp9423's avatar
alp9423
Copper Contributor
Aug 02, 2022

IF AND Formula help

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)))

 

 

 

excel

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 02, 2022

    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))

    • alp9423's avatar
      alp9423
      Copper Contributor
      Aug 02, 2022

      HansVogelaar 

       

      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))))

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 02, 2022

        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)

Resources