+ Reply to Thread
Results 1 to 3 of 3

Improve formula - Find a way around Nestled IF statement

  1. #1
    Registered User
    Join Date
    10-05-2007
    Posts
    2

    Improve formula - Find a way around Nestled IF statement

    I am trying to take the sum of cells in one column (D) based on concurrent cells in another column (F). This is the formula I am currently using which works well, however I need more then 7 IF statements:

    =IF(F2<>F3,D2,IF(F3<>F4,SUM(D2:D3),IF(F4<>F5,SUM(D2:D4),IF(F5<>F6,SUM(D2:D5),IF(F6<>F7,SUM(D2:D6),IF(F7<>F8,SUM(D2:D7),IF(F8<>F9,SUM(D2:D8),SUM(D2:D9))))))))


    Does anyone know a better way to write this formula so it can take a larger range of cells in column F and/or improve the method?

    Thanks

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Still quite long but you should be able to extend this for ranges as long as you want

    =SUM(D2:IF(COUNTIF(F2:F9,F2)=ROWS(F2:F9),D9,INDEX(D2:D8,MATCH(TRUE,INDEX(F2:F8<>F3:F9,0),0))))

  3. #3
    Registered User
    Join Date
    10-05-2007
    Posts
    2
    Thank you very much daddylonglegs! It works like a charm.

+ 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