Multiple If in SumIfs formula

1. 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. 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)))

3. 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?

4. 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. 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. 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. 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.

8. 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. 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

10. 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. Re: Multiple If in SumIfs formula

Originally Posted by Haseeb A
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

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

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