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

 

[Weazel]

Its not my intention to hijack this thread but I was looking at this earlier today and was struggling with the logic.

I ended up putting the data in a DSUM but the criteria seemed to be kind of complicated.

is there a better way to present the Criteria to the DSUM or am I over complicating things?

I appreciate anybody's input.



Excel 2012

	A	B	C	D	E	F	G	H	I	J
1	Data							Criteria
2	Material	Vendor	Plant	Spend		691		Material	Vendor	Plant
3	123	234	1	100				123	111	1
4	123	234	2	150				123	234	1
5	123	234	2	110				321	111	1
6	123	111	2	130				321	234	1
7	321	111	1	101				123	111	2
8	123	111	1	100				123	234	2
9	321	124	2	110				321	111	2
10	543	259	1	120				321	234	2
11	543	234	1	130

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula F2 =DSUM(A2:D11,4,H2:J10) <tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

Its not my intention to hijack this thread but I was looking at this earlier today and was struggling with the logic.

I ended up putting the data in a DSUM but the criteria seemed to be kind of complicated.

is there a better way to present the Criteria to the DSUM or am I over complicating things?

I appreciate anybody's input.

...

See

http://www.mrexcel.com/forum/excel-questions/58539-horizontal-criteria-null-value.html

various methods.

 

[billszysz]

Aladin,
I'm sorry that I came out during the discussion but I had to.
Just checked these formulas and Brian's is almost 2 times faster for 10,000 rows. (but we still talking about split second- in my computer)
So i agree with your suggestion.
We can make this formula almost maintenance-free if we use tables for main range and criteria ranges.
Greetings

 

[Aladin Akyurek]

Aladin,
I'm sorry that I came out during the discussion but I had to.
Just checked these formulas and Brian's is almost 2 times faster for 10,000 rows. (but we still talking about split second- in my computer)
So i agree with your suggestion.
We can make this formula almost maintenance-free if we use tables for main range and criteria ranges.
Greetings

It seems the table functionality has some performance problems (according to what some users here mention), probably due to the overhead it must be creating...