SUMIFS combined with OR statement? Multiple possibilities for multiple criteria...?

[Emma_S]

Hi
I don't normally post in forums as generally find someone else has already asked/answered my questions but I just can't solve/find the answer to this one so would appreciate any help you can give! I also was not able to download the .xla to post a table using the required system so have tried to improvie below.

I am using Excel 2010 and trying to do a SUMIFS formula to return the sum value for all possible combinations of the multiple values entered as criteria.
E.g. below is the example data set (real data set is hundreds of lines long and using pivots is NOT an option as I need to the sum the data up different ways on different tabs. Data tab is a download from BW and on a tab on its own, Projects that need to pull from the BW data are on other tabs. in additon to below data & criteria I have year and month in my real data which are also criteria in the look up)
_____ A ___________B __________C _______D
1 ____Material ______Vendor_____ Plant_____Spend
2 ____123________ 234 ______1________100
3____123 __________234________ 2 ________150
4____321 __________234________ 2 ________110
5____123 __________245 ________1________ 120
6____321 __________111________2 ________130
7____321 __________111 ________1 ________101
8____123 __________111 ________1 ________100
9____321__________ 124 ________2 ________110
10 ___543 __________259 ________1 ________120
11 ___543 __________234 ________1 ________130
12
13 Criteria
14 ____Plant__________1
15 __________________2
16 ____Vendor _______234
17__________________111
18____Material _______123
19__________________321

I want to use a SUMIFS formula to return the spend for all combinations of Material 123 or 234 and vendor 234 or 111 and plant 1 or 2.
When I try the formula
=SUMPRODUCT(SUMIFS($D$2:$D$11,$a$2:$A$11,B18:B19,$B$2:$B$11,B14:B15,$C$2:$C$11,B14:B15))

I only get the result 230. That is the red bold rows. But I want it to return the results for ALL the red rows (which should be 691).

I tried with an OR formula in the criteria part but can't get it to work. While searching the internet I saw some posts about the { } to simulate the OR funcation but can't get that to work for the multiple OR criteria I have. I can get it to work if I have 2 vendors and one material and one plant.. But not if there are 2 vendors & 2 materials & 2 plants (so 6 possible unique combinations of the variables).

Any help by anyone that can get it to work without having to do multiple sumifs statements would be greatly appreciated?. I currenlty have it set up with multiple sumifs but it would be much more userfiendly if I could get it work as I want so it is easier for the end user of the workbook to use and maintain as new project tabs are added to the file.

Thanks in advance for your help!!
Emma

 

[billszysz]

Hi Emma_S,

Arithmetic operations will solve your problem but SUMIFS don't.
Try this

=SUMPRODUCT((($A$2:$A$11=B18)+($A$2:$A$11=B19))*(($B$2:$B$11=B16)+($B$2:$B$11=B17))*(($C$2:$C$11=B14)+($C$2:$C$11=B15))*$D$2:$D$11)

 

[Brian from Maui]

Try something like......

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{123,321},0))),--ISNUMBER(MATCH(B1:B10,{234,111},0)),--ISNUMBER(MATCH(C1:C10,{1,2},0)),D1:D10)

 

[billszysz]

You can also try this construction (better than arithmetic operations).
In this formula you can use different count of item for each criteria.

=SUM(
MMULT(--($A$2:$A$11=TRANSPOSE($B$18:$B$19)),ROW($B$18:$B$19)^0)*
MMULT(--($B$2:$B$11=TRANSPOSE($B$16:$B$17)),ROW($B$16:$B$17)^0)*
MMULT(--($C$2:$C$11=TRANSPOSE($B$14:$B$15)),ROW($B$14:$B$15)^0)*
$D$2:$D$11)

oops...this is array formula so CTRL+SHIFT+ENTER

 

[Aladin Akyurek]

You can also try this construction (better than arithmetic operations).
In this formula you can use different count of item for each criteria.

=SUM(
MMULT(--($A$2:$A$11=TRANSPOSE($B$18:$B$19)),ROW($B$18:$B$19)^0)*
MMULT(--($B$2:$B$11=TRANSPOSE($B$16:$B$17)),ROW($B$16:$B$17)^0)*
MMULT(--($C$2:$C$11=TRANSPOSE($B$14:$B$15)),ROW($B$14:$B$15)^0)*
$D$2:$D$11)

oops...this is array formula so CTRL+SHIFT+ENTER

Too costly.

 

[billszysz]

Yes, it is costly, I absolutely agree but.....easy to change if numbers of item in criteria can changes....hmm... maybe this is more important for user?

 

[Aladin Akyurek]

Yes, it is costly, I absolutely agree but.....easy to change if numbers of item in criteria can changes....hmm... maybe this is more important for user?

You can replace the constant arrays with ranges in Brian's suggestion for flexibility and ease.

 

[Emma_S]

Thanks all for your replies and help.

Brian's formula with arrays instead of the hard coding the criteria works perfectly and is easy to change. (I want to avoid array formulas as most people in my organisation won't have a clue about them and it could lead to errors if they forget to hit ctrl+shift+enter after being in the cell.

I would never have thought of using the match and sumproduct to return the spend. Perfect - thank you so much! Just one question - for the match function you normally do match(lookup_value,lookup_array,0). But you have it match(lookup_array,lookup_value,0) and indeed in this instance it only works this way. Why? Is it related to using multiple criteria for it to match? Just trying to fully understand the formula. Thanks again!!

 

[Aladin Akyurek]

Thanks all for your replies and help.

Brian's formula with arrays instead of the hard coding the criteria works perfectly and is easy to change. (I want to avoid array formulas as most people in my organisation won't have a clue about them and it could lead to errors if they forget to hit ctrl+shift+enter after being in the cell.

I would never have thought of using the match and sumproduct to return the spend. Perfect - thank you so much! Just one question - for the match function you normally do match(lookup_value,lookup_array,0). But you have it match(lookup_array,lookup_value,0) and indeed in this instance it only works this way. Why? Is it related to using multiple criteria for it to match? Just trying to fully understand the formula. Thanks again!!

MATCH(Range,List,0)

==>

{1;#N/A;#N/A,1,3,#N/A,...}

Fed to ISNUMBER...

ISNUMBER(MATCH(Range,List,0))

==>

{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;...}

This coerced into 1's and 0's using -- in front...

==>

{1;0;0;1,1,0;...}

SumProduct understands the last result because it is numeric.

Note that those 1's are expected intermediate evaluations.

 

[Brian from Maui]

Thanks all for your replies and help.

Brian's formula with arrays instead of the hard coding the criteria works perfectly and is easy to change. (I want to avoid array formulas as most people in my organisation won't have a clue about them and it could lead to errors if they forget to hit ctrl+shift+enter after being in the cell.

I would never have thought of using the match and sumproduct to return the spend. Perfect - thank you so much! Just one question - for the match function you normally do match(lookup_value,lookup_array,0). But you have it match(lookup_array,lookup_value,0) and indeed in this instance it only works this way. Why? Is it related to using multiple criteria for it to match? Just trying to fully understand the formula. Thanks again!!

Emma,

Never really thought about it until now.........

Glad Aladin came by to explain.