Nested IF
 

Hi,

I have six nested IFs as follows:

=IF(AND(AND(DATE(2006,7,21)=TODAY(),DATE(2006,7,1 0)<=TODAY()),T7="E"),SUMPRODUCT(--(K7:S7<"E"),--(K7:S7<"OC"),--(K7:S7<"V"),--(K7:S7<""),K7:S7),IF(AND(AND(DATE(2006,7,28)=TOD AY(),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)=TODA Y(),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)=T ODAY(),DATE(2006,7,31)<=TODAY()),AI7="E"),SUMPRODU CT(--(Z7:AH7<"E"),--(Z7:AH7<"OC"),--(Z7:AH7<"V"),--(Z7:AH7<""),Z7:AH7),IF(AND(AND(DATE(2006,8,18)=T ODAY(),DATE(2006,8,7)<=TODAY()),AN7="E"),SUMPRODUC T(--(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"),SUMPRO DUCT(--(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 I
omit the last IF, it fine, but I can't determine what the error is.
Can anyone help?

Thanks,
Gos-C


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=560181

Nested IF
 

OK, I found the error - I was using too many AND. The correct formula
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)=TOD AY(),DATE(2006,8,14)<=TODAY(),AS7="E"),SUMPRODUCT(--(AJ7:AR7<"E"),--(AJ7:AR7<"OC"),--(AJ7:AR7<"V"),--(AJ7:AR7<""),AJ7:AR7),0))))))


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=560181

Nested IF
 

OK, I found the error - I was using too many AND. The correct formula
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)=TOD AY(),DATE(2006,8,14)<=TODAY(),AS7="E"),SUMPRODUCT(--(AJ7:AR7<"E"),--(AJ7:AR7<"OC"),--(AJ7:AR7<"V"),--(AJ7:AR7<""),AJ7:AR7),0))))))


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=560181