SUMIFS With Multiple Criteria In One Column

[excelbytes]

I have a formula that works to sum up the data with multiple criteria in one column:

=SUM(SUMIFS(DATA!$I:$I,DATA!$C:$C,$A$10,DATA!$G:$G,F$4,DATA!$E:$E,$A$20:$A$27,DATA!$A:$A,$B$2,DATA!$F:$F,$B$1))

However, I have a need to multiple criteria in each of two columns:

=SUM(SUMIFS(DATA!$I:$I,DATA!$C:$C,$A$10:$A$17,DATA!$G:$G,F$4,DATA!$E:$E,$A$20:$A$27,DATA!$A:$A,$B$2,DATA!$F:$F,$B$1))

But that doesn't work. Is there a way to accomplish this? If not with a formula, is there a VBA solution?

 

[KRice]

Try this:

=SUM(SUMIFS(DATA!$I:$I,DATA!$C:$C,TRANSPOSE($A$10:$A$17),DATA!$G:$G,F$4,DATA!$E:$E,$A$20:$A$27,DATA!$A:$A,$B$2,DATA!$F:$F,$B$1))

 

[excelbytes]

Try this:

=SUM(SUMIFS(DATA!$I:$I,DATA!$C:$C,TRANSPOSE($A$10:$A$17),DATA!$G:$G,F$4,DATA!$E:$E,$A$20:$A$27,DATA!$A:$A,$B$2,DATA!$F:$F,$B$1))

It works perfectlym Thanks! Can you explain why?

 

[excelbytes]

It works perfectlym Thanks! Can you explain why?

Actually, there is an issue. It worked fine until I added more criteria. See below. When I had Ball and Crown in the Customers, it was fine, but when I added Springs, it didn't include those in the totals. I'm guessing it can only handle items in a certain order. Can you explain?

SUMIFS Multi Criteria.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1							Co	Accounts AOP File	Corporate Name	Account Name	Item Group	Account Manager	Month	Quarter	Forecast
2									Springs		Spares		Apr-24		85000
3									Ball		Comp		Apr-24		60000
4									Crown		Spares		Apr-24		37500
5									Ball		Comp		Apr-24		25000
6									Ball		Comp		Apr-24		24000
7									Crown		Spares		Apr-24		15400
8									Crown		Comp		Apr-24		15000
9	Customer								Crown		Comp		Apr-24		12000
10	Ball								Crown		Comp		Apr-24		10000
11	Crown								Crown		Comp		Apr-24		10000
12	Springs								Ball		Comp		Apr-24		8400
13									Crown		Comp		Apr-24		6000
14									Ball		Spares		Apr-24		5000
15									Crown		Comp		Apr-24		5000
16									Crown		Comp		Apr-24		2500
17									Crown		Spares		Apr-24		2000
18									Springs		Comp. Spares		Apr-24		1500
19	Groups
20	Comp
21	Spares
22
23
24
25
26
27
28
Sheet1

 

[KRice]

SUMIFS will allow up to two “OR”-type criteria, but the criteria need to be expressed differently: one as a horizontal array, the other as a vertical array. Otherwise, SUMIFS would have no way of handling the two sets of OR conditions. Taking your original formula as an example, where you had A20:A27, SUMIFS treats this as a single vertical array consisting of 8-elements (rows), and the other criteria in SUMIFS (the other AND conditions) are applied to each of these rows, for a total of 8 sets of logical AND conditions that are applied to the data found on the DATA sheet. So SUMIFS returns 8 sums, and these are finally wrapped in a SUM function to obtain the grand total.

If you then want a 2nd set of OR conditions, we can't use another vertical array because there would be ambiguity regarding how the expression should be interpreted. Instead, the criteria array is transposed to create a single row array (TRANSPOSE(A20:A27)...so a single row, 8-column array), and the other criteria in SUMIFS, including the other "OR" criteria, are applied to each of these columns, to effectively generate a set of 64 logical expressions that are applied to the data in the DATA sheet. And the resulting 64 sums from the SUMIFS functions are summed together by the SUM wrapper function.

This method should work because it exploits SUMIFS ability to handle two-dimensional arrays; however, a 3rd OR condition would require a different approach.

I don't see any problem with the example you posted. You've shifted some columns, so the formula above needs some adjustment. Have a look at this example...compare the manual sum checked in P1 of DATA with the formula result in C6 of Sheet1.

MrExcel_20240108.xlsx
	A	B	C	D	E	F
1	Data L crit	b
2	Data A crit	12a
3
4					Data M crit	4/1/2024
5
6	11	Result summing Data O	29
7
8
9	Customer
10	Ball	Data I crit (single)
11	Crown	Data I crit (expanded)
12	Springs
13
14
15
16
17
18
19	Groups
20	Comp	Data K crit
21	Spares
22
23
24
25
26
27
Sheet1

Cell Formulas
Range	Formula
A6	A6	=SUM(SUMIFS(DATA!$O:$O,DATA!$I:$I,$A$10,DATA!$M:$M,F$4,DATA!$K:$K,$A$20:$A$27,DATA!$A:$A,$B$2,DATA!$L:$L,$B$1))
C6	C6	=SUM(SUMIFS(DATA!$O:$O,DATA!$I:$I,TRANSPOSE($A$10:$A$17),DATA!$M:$M,F$4,DATA!$K:$K,$A$20:$A$27,DATA!$A:$A,$B$2,DATA!$L:$L,$B$1))

MrExcel_20240108.xlsx
	A								I	J	K	L	M	N	O	P
1									Corporate Name		Item Group	Account Manager	Month		Forecast	29
2	12a								Springs		Spares	b	Apr-24		1
3	12a								Ball		Comp	b	Apr-24		2
4	12a								Crown		Spares	b	Apr-24		3
5	12a								Ball		Comp	b	Apr-24		4
6	12a								Ball		Comp	b	Apr-24		5
7	12a								Crown		Spares	b	Apr-24		6
8	22b								Crown		Comp	b	Apr-24		7
9	12a								Crown		Comp	b	Apr-24		8
10	22b								Crown		Comp	b	Apr-24		9
11									Crown		Comp	b	Apr-24		10
12									Ball		Comp	b	Apr-24		11
13									Crown		Comp	b	Apr-24		12
14									Ball		Spares	b	Apr-24		13
15									Crown		Comp	b	Apr-24		14
16									Crown		Comp	b	Apr-24		15
17									Crown		Spares	b	Apr-24		16
18									Springs		Comp. Spares	b	Apr-24		17
DATA

Cell Formulas
Range	Formula
P1	P1	=SUM(O2:O7,O9)

 

[excelbytes]

I think this is working correctly. I need to spend more time analyzing it. This is a 90 Mb file and it may just be an issue with some corrupt data. Thanks again for all your help.

 

[KRice]

I'm happy to help. Post back if you discover any surprises. I've tested further with the small example above--by excluding items in each of the AND conditions and adding additional OR match items to "Customer" and "Groups"...and then comparing the formula results to a manual sum. I haven't encountered any discrepancies.

...it may just be an issue with some corrupt data

This is definitely something to consider. An extra space or misspelling will exclude items that otherwise would be included.

 

[excelbytes]

One last question (I hope)...

Here is my final formula which is working:

=IF(A4="CORPORATE CUSTOMER",SUM(SUMIFS('AOP DATA'!$I:$I,'AOP DATA'!$C:$C,TRANSPOSE($A$10:$A$14),'AOP DATA'!$G:$G,F$4,'AOP DATA'!$E:$E,$A$17:$A$21,'AOP DATA'!$A:$A,$B$2,'AOP DATA'!$F:$F,$B$1)),SUM(SUMIFS('AOP DATA'!$I:$I,'AOP DATA'!$D:$D,TRANSPOSE($A$10:$A$14),'AOP DATA'!$G:$G,F$4,'AOP DATA'!$E:$E,$A$17:$A$21,'AOP DATA'!$A:$A,$B$2,'AOP DATA'!$F:$F,$B$1)))

Is it possible to have an IF function inside a SUMIFS function? What I'm trying to accomplish is, for example, one of the components of the formula is:

'AOP DATA'!$F:$F,$B$1

In this scenario, $B$1 is a list of employees. On that list, I have a selection "All". What I want to do is say, if $B$1 = "All" ignore this criteria range and criteria, else use the criteria range and criteria indicated. I've done this type of thing before with only one option of "All", which is have an IF statement up front that has one formula with that criteria range and criteria and the other without, based on that selection. But I may have more than one is this scenario, so I thought if I can imbed IF statements in the SUMIFS formula, that could work.

 

[KRice]

Yes, that should work. See this example. I've created a unique list of employees for my column L of the Data sheet and appended that list to "All" (see formula in C14). That spilling list is used in Data Validation in B1. For B1="b", a total of 48 is returned; B1="Z" yields a total of 4, and B1="All" gives 52. See how the wildcard is used in the embedded IF statement.

MrExcel_20240108.xlsx
	A	B	C	D	E	F
1	Data L crit	All
2	Data A crit	12a
3
4					Data M crit	4/1/2024
5
6	0	Result summing Data O	52
7
8
9	Customer
10	Ball	Data I crit (single)
11	Crown	Data I crit (expanded)
12	Springs
13			List of Employees
14			All
15			b
16			Z
17
18
19	Groups
20	Comp	Data K crit
21	Spares
22	Rejects
23	Reinspections
24
25
26
27
Sheet1

Cell Formulas
Range	Formula
A6	A6	=SUM(SUMIFS(DATA!$O:$O,DATA!$I:$I,$A$10,DATA!$M:$M,F$4,DATA!$K:$K,$A$20:$A$27,DATA!$A:$A,$B$2,DATA!$L:$L,$B$1))
C6	C6	=SUM(SUMIFS(DATA!$O:$O,DATA!$I:$I,TRANSPOSE($A$10:$A$17),DATA!$M:$M,F$4,DATA!$K:$K,$A$20:$A$27,DATA!$A:$A,$B$2,DATA!$L:$L,IF($B$1="All","*",$B$1)))
C14:C16	C14	=VSTACK("All",SORT(UNIQUE(DATA!L2:L18)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=$C$14#

Almost forgot...here's the Data sheet used in this example:

MrExcel_20240108.xlsx
	A								I	J	K	L	M	N	O
1									Corporate Name		Item Group	Account Manager	Month		Forecast
2	12a								Springs		Spares	b	Apr-24		1
3	12a								Ball		Comp	b	Apr-24		2
4	12a								Crown		Spares	b	May-24		3
5	12a								Ball		Comp	Z	Apr-24		4
6	12a								Ball		Not Comp	b	Apr-24		5
7	12a								Not Crown		Spares	b	Apr-24		6
8	22b								Crown		Comp	b	Apr-24		7
9	12a								Crown		Rejects	b	Apr-24		8
10	22b								Crown		Comp	b	Apr-24		9
11	12a								Crown		Rejects	b	Apr-24		10
12	22b								Ball		Comp	b	Apr-24		11
13	22b								Crown		Comp	b	Apr-24		12
14	12a								Ball		Reinspections	b	Apr-24		13
15	12a								Crown		Rejects	b	Apr-24		14
16	22b								Crown		Comp	b	Apr-24		15
17	22b								Crown		Spares	b	Apr-24		16
18	22b								Springs		Comp. Spares	b	Apr-24		17
DATA

 

[excelbytes]

However when I try it on a small test sheet, I get 0 when I choose all:

=SUM(SUMIFS($K$2:$K$18,$J$2:$J$18,O2,$H$2:$H$18,TRANSPOSE($M$2:$M$9),$I$2:$I$18,$M$12:$M$19,$G$2:$G$18,IF($N$1="All","*",$N$1)))

SUMIFS Multi Criteria.xlsm
	G	H	I	J	K	L	M	N	O	P
1	Co	Corporate Name	Item Group	Month	Forecast		Customer	All
2	100	Springs	Spares	Apr-24	85000		Springs		Apr-24	May-24
3	100	Springs	Comp	May-24	60000		Ball		0	0
4	500	Springs	Spares	Apr-24	37500
5	500	Springs	Comp	May-24	25000
6	100	Springs	Spares	Apr-24	24000
7	100	Springs	Comp	May-24	15400
8	500	Springs	Spares	Apr-24	15000
9	500	Ball	Comp	May-24	12000
10	100	Ball	Spares	Apr-24	10000
11	100	Ball	Comp	May-24	10000		Groups
12	500	Ball	Spares	Apr-24	8400		Spares
13	500	Ball	Comp	May-24	6000		Comp
14	100	Ball	Spares	Apr-24	5000
15	100	Ball	Comp	May-24	5000
16	500	Ball	Spares	Apr-24	2500
17	500	Ball	Comp	May-24	2000
18	100	Ball	Spares	Apr-24	1500
19
20
Sheet1

Cell Formulas
Range	Formula
O3:P3	O3	=SUM(SUMIFS($K$2:$K$18,$J$2:$J$18,O2,$H$2:$H$18,TRANSPOSE($M$2:$M$9),$I$2:$I$18,$M$12:$M$19,$G$2:$G$18,IF($N$1="All","*",$N$1)))

Cells with Data Validation
Cell	Allow	Criteria
N1	List	100,500,All