+ Reply to Thread
Results 1 to 11 of 11

Multiple If in SumIfs formula

  1. #1
    Registered User
    Join Date
    01-23-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Multiple If in SumIfs formula

    Hi all

    I am using sumifs to calculate multiple items based multiple criteria. My problems is that I have 5 key main filter points lets say A,B and C and these are them filtered by week D.

    I want week to calculate always by with the A,B,and C there are multiple sub categories. My problem is that within this I wish to put an "All" option that will calculate all values within the categories based on the other categories or sub categories. This needs to work for all options and if 'All' is selected on all categories it will calculate the total for everything for the week (the week in formula below is "Raw!B:B,'Weekly Overview'!B11") I have been able to get an if function to work for just one category were if "All" is selected it calculates the entire category. When I try to use multiple if functions in the sumif I get #value error.

    =IF($C$7="All",(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!$C$5,Raw!C:C,'Weekly Overview'!$C$6,Raw!B:B,'Weekly Overview'!B11)),(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!$C$5,Raw!C:C,'Weekly Overview'!$C$6,Raw!N:N,'Weekly Overview'!$C$7,Raw!B:B,'Weekly Overview'!B11)))

    This is the formula i have used so far and am unsure how to structure the remaining.

    Hope someone can assist. I have read other posts and wasn't able to find the answer but if it has been posted please point me in the right direction.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Multiple If in SumIfs formula

    It might be easier to use SUMPRODUCT.....but also it's better to restrict the range, e.g. with data from row 2 to 100 (adjust as required) try

    =SUMPRODUCT(Raw!H2:H100,(Raw!A2:A100=IF($C$5,"All",Raw!A2:A100,$C$5))*(Raw!C2:C100=IF($C$6,"All",Raw!C2:C100,$C$6))*(Raw!N2:N100=IF($C$7,"All",Raw!N2:N100,$C$7))*(Raw!B2:B100=IF(B11,"All",Raw!B2:B100,B11)))
    Audere est facere

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple If in SumIfs formula

    It looks like it should work, if you say that the original SUMIFS worked...

    Can we see a sample workbook?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    01-23-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiple If in SumIfs formula

    The sumif is it stands does work. The error occurs when I use try to use multiples if as in:

    =IF($C$7="All",(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!$C$5,Raw!C:C,'Weekly Overview'!$C$6,Raw!B:B,'Weekly Overview'!B10)),(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!$C$5,Raw!C:C,'Weekly Overview'!$C$6,Raw!N:N,'Weekly Overview'!$C$7,Raw!B:B,'Weekly Overview'!B10))), IF($C$6="All",(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!$C$7,Raw!B:B,'Weekly Overview'!$C$6,Raw!B:B,'Weekly Overview'!B10)),(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!$C$5,Raw!C:C,'Weekly Overview'!$C$6,Raw!N:N,'Weekly Overview'!$C$7,Raw!B:B,'Weekly Overview'!B10)))

    This returns an error. Is it incorrect?

    Thanks

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple If in SumIfs formula

    The 2 IF formulas should be separated.

    Can you post a sample workbook detailing what you are trying to accomplish?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple If in SumIfs formula

    The 2 IF formulas should be separated.

    Can you post a sample workbook detailing what you are trying to accomplish?

  7. #7
    Registered User
    Join Date
    01-23-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiple If in SumIfs formula

    I need to have a large range as the report is update regularly. However this is the formula when I try to write it using multiple ifs.

    =IF($C$7="All",(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!$C$5,Raw!C:C,'Weekly Overview'!$C$4,Raw!B:B,'Weekly Overview'!B10)),(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!$C$5,Raw!C:C,'Weekly Overview'!$C$4,Raw!N:N,'Weekly Overview'!$C$7,Raw!B:B,'Weekly Overview'!B10))),IF(C4="All",(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!C5,Raw!N:N,'Weekly Overview'!C7,Raw!B:B,'Weekly Overview'!B10)),(SUMIFS(Raw!H:H,Raw!A:A,'Weekly Overview'!C5,Raw!C:C,'Weekly Overview'!C4,Raw!N:N,'Weekly Overview'!C7,Raw!B:B,'Weekly Overview'!B10)))

    It returns a #value error. What am I doing wrong?

    File attached. I wish to be able to select all for each dropdown and for it to calculate all valuse for the given week based on remaining criterea.
    Attached Files Attached Files
    Last edited by notimepelican; 01-23-2012 at 12:39 PM. Reason: Added workbox

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Multiple If in SumIfs formula

    I made an error in my earlier SUMPRODUCT suggestion....but I suggest that's possibly still the best way to go, otherwise you will need multiple IFs and a very long formula. For example this version in D10 copied down

    =SUMPRODUCT(Raw!$H$2:$H$1000,(Raw!$A$2:$A$1000=IF($C$5="All",Raw!$A$2:$A$1000,$C$5))*(Raw!$C$2:$C$1000=IF($C$4="All",Raw!$C$2:$C$1000,$C$4))*(Raw!$N$2:$N$1000=IF($C$7="All",Raw!$N$2:$N$1000,$C$7))*(Raw!$L$2:$L$1000=IF($C$6="All",Raw!$L$2:$L$1000, $C$6))*(Raw!$B$2:$B$1000=$B10))

  9. #9
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Multiple If in SumIfs formula

    You only have 1 numeric field (week) in Criteria_range also that one doesn't have a 'All' option, so try to use "*" in SUMIFS when select 'All' in each field. Try this,

    =SUMIFS(Raw!$H:$H,Raw!$A:$A,IF($C$5="All","*",$C$5),Raw!$C:$C,IF($C$4="All","*",$C$4),Raw!$B:$B,$B10,Raw!$L:$L,IF($C$6="All","*",$C$6),Raw!$N:$N,IF($C$7="All","*",$C$7))

    copy down
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Multiple If in SumIfs formula

    Hey, that's a good suggestion Haseeb. That seems to give the same results as my suggestion but SUMIFS is better.....

  11. #11
    Registered User
    Join Date
    01-23-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiple If in SumIfs formula

    Quote Originally Posted by Haseeb A View Post
    You only have 1 numeric field (week) in Criteria_range also that one doesn't have a 'All' option, so try to use "*" in SUMIFS when select 'All' in each field. Try this,

    =SUMIFS(Raw!$H:$H,Raw!$A:$A,IF($C$5="All","*",$C$5),Raw!$C:$C,IF($C$4="All","*",$C$4),Raw!$B:$B,$B10,Raw!$L:$L,IF($C$6="All","*",$C$6),Raw!$N:$N,IF($C$7="All","*",$C$7))

    copy down
    Thank you all for the suggestions.

    Haseeb your soloution has done the job

    Thank you

+ 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