+ Reply to Thread
Results 1 to 2 of 2

Nested IF

  1. #1
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    408

    Question Nested IF

    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 I omit the last IF, it fine, but I can't determine what the error is. Can anyone help?

    Thanks,
    Gos-C
    Using Excel 2010 & Windows 10
    "It is better to be prepared for an opportunity and not have one than to have an opportunity and not be prepared."

  2. #2
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    408
    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)>=TODAY(),DATE(2006,8,14)<=TODAY(),AS7="E"),SUMPRODUCT(--(AJ7:AR7<>"E"),--(AJ7:AR7<>"OC"),--(AJ7:AR7<>"V"),--(AJ7:AR7<>""),AJ7:AR7),0))))))
    Last edited by Gos-C; 07-11-2006 at 11:55 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1