Nested IF

[Gos-C]

Hi,

I have six nested IFs as follows:

=IF(AND(AND(DATE(2006,7,21)>=TODAY(),DATE(2006,7,10)<=TODAY()),T7="E"),SUMPRODUCT(--(K7:S7<>"E"),--(K7:S7<>"OC"),--(K7:S7<>"V"),--(K7:S7<>""),K7:S7),IF(AND(AND(DATE(2006,7,28)>=TODAY(),DATE(2006,7,17)<=TODAY()),Y7="E"),SUMPRODUCT(--(P7:X7<>"E"),--(P7:X7<>"OC"),--(P7:X7<>"V"),--(P7:X7<>""),P7:X7),IF(AND(AND(DATE(2006,8,4)>=TODAY(),DATE(2006,7,24)<=TODAY()),AD7="E"),SUMPRODUCT(--(U7:AC7<>"E"),--(U7:AC7<>"OC"),--(U7:AC7<>"V"),--(U7:AC7<>""),U7:AC7),IF(AND(AND(DATE(2006,8,11)>=TODAY(),DATE(2006,7,31)<=TODAY()),AI7="E"),SUMPRODUCT(--(Z7:AH7<>"E"),--(Z7:AH7<>"OC"),--(Z7:AH7<>"V"),--(Z7:AH7<>""),Z7:AH7),IF(AND(AND(DATE(2006,8,18)>=TODAY(),DATE(2006,8,7)<=TODAY()),AN7="E"),SUMPRODUCT(--(AE7:AM7<>"E"),--(AE7:AM7<>"OC"),--(AE7:AM7<>"V"),--(AE7:AM7<>""),AE7:AM7),IF(AND(AND(DATE(2006,8,25)>=TODAY(),DATE(2006,8,14)<=TODAY()),AS7="E"),SUMPRODUCT(--(AJ7:AR7<>"E"),--(AJ7:AR7<>"OC"),--(AJ7:AR7<>"V"),--(AJ7:AR7<>""),AJ7:AR7),0))))))

It's evaluating to "The formula you typed contains an error." If
omit the last IF, it fine, but I can't determine what the error is.
Can anyone help?

Thanks,
Gos-

 

[Gos-C]

OK, I found the error - I was using too many AND. The correct formul
is:

=IF(AND(DATE(2006,7,21)>=TODAY(),DATE(2006,7,10)<=TODAY(),T7="E"),SUMPRODUCT(--(K7:S7<>"E"),--(K7:S7<>"OC"),--(K7:S7<>"V"),--(K7:S7<>""),K7:S7),IF(AND(DATE(2006,7,28)>=TODAY(),DATE(2006,7,17)<=TODAY(),Y7="E"),SUMPRODUCT(--(P7:X7<>"E"),--(P7:X7<>"OC"),--(P7:X7<>"V"),--(P7:X7<>""),P7:X7),IF(AND(DATE(2006,8,4)>=TODAY(),DATE(2006,7,24)<=TODAY(),AD7="E"),SUMPRODUCT(--(U7:AC7<>"E"),--(U7:AC7<>"OC"),--(U7:AC7<>"V"),--(U7:AC7<>""),U7:AC7),IF(AND(DATE(2006,8,11)>=TODAY(),DATE(2006,7,31)<=TODAY(),AI7="E"),SUMPRODUCT(--(Z7:AH7<>"E"),--(Z7:AH7<>"OC"),--(Z7:AH7<>"V"),--(Z7:AH7<>""),Z7:AH7),IF(AND(DATE(2006,8,18)>=TODAY(),DATE(2006,8,7)<=TODAY(),AN7="E"),SUMPRODUCT(--(AE7:AM7<>"E"),--(AE7:AM7<>"OC"),--(AE7:AM7<>"V"),--(AE7:AM7<>""),AE7:AM7),IF(AND(DATE(2006,8,25)>=TODAY(),DATE(2006,8,14)<=TODAY(),AS7="E"),SUMPRODUCT(--(AJ7:AR7<>"E"),--(AJ7:AR7<>"OC"),--(AJ7:AR7<>"V"),--(AJ7:AR7<>""),AJ7:AR7),0)))))

 

[Gos-C]

OK, I found the error - I was using too many AND. The correct formul
is:

=IF(AND(DATE(2006,7,21)>=TODAY(),DATE(2006,7,10)<=TODAY(),T7="E"),SUMPRODUCT(--(K7:S7<>"E"),--(K7:S7<>"OC"),--(K7:S7<>"V"),--(K7:S7<>""),K7:S7),IF(AND(DATE(2006,7,28)>=TODAY(),DATE(2006,7,17)<=TODAY(),Y7="E"),SUMPRODUCT(--(P7:X7<>"E"),--(P7:X7<>"OC"),--(P7:X7<>"V"),--(P7:X7<>""),P7:X7),IF(AND(DATE(2006,8,4)>=TODAY(),DATE(2006,7,24)<=TODAY(),AD7="E"),SUMPRODUCT(--(U7:AC7<>"E"),--(U7:AC7<>"OC"),--(U7:AC7<>"V"),--(U7:AC7<>""),U7:AC7),IF(AND(DATE(2006,8,11)>=TODAY(),DATE(2006,7,31)<=TODAY(),AI7="E"),SUMPRODUCT(--(Z7:AH7<>"E"),--(Z7:AH7<>"OC"),--(Z7:AH7<>"V"),--(Z7:AH7<>""),Z7:AH7),IF(AND(DATE(2006,8,18)>=TODAY(),DATE(2006,8,7)<=TODAY(),AN7="E"),SUMPRODUCT(--(AE7:AM7<>"E"),--(AE7:AM7<>"OC"),--(AE7:AM7<>"V"),--(AE7:AM7<>""),AE7:AM7),IF(AND(DATE(2006,8,25)>=TODAY(),DATE(2006,8,14)<=TODAY(),AS7="E"),SUMPRODUCT(--(AJ7:AR7<>"E"),--(AJ7:AR7<>"OC"),--(AJ7:AR7<>"V"),--(AJ7:AR7<>""),AJ7:AR7),0)))))