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]

I suspect the list used by data validation is taken as a list of text values, while your column G used for matching inside SUMIFS consists of numeric values. Does this example reflect the real-life case? If so, some adjustments are needed to maintain the same data types. As a quick test, change the column G values to text by placing a ` (the character in the upper left of the keyboard typically paired with ~) in front of a few of the numbers that should be summed. EDIT: I don't think this is correct...I'll look over more carefully.

By the way...you could eliminate some redundancy in your earlier formula by incorporating the IF "CORPORATE CUSTOMER" component inside the SUMIFS, as that seems to be the only reason for using one column vs. another column in SUMIFS:

=SUM(SUMIFS(DATA!$O:$O,IF(A4="CORPORATE CUSTOMER",DATA!$I:$I,DATA!$J:$J),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)))

 

[excelbytes]

I suspect the list used by data validation is taken as a list of text values, while your column G used for matching inside SUMIFS consists of numeric values. Does this example reflect the real-life case? If so, some adjustments are needed to maintain the same data types. As a quick test, change the column G values to text by placing a ` (the character in the upper left of the keyboard typically paired with ~) in front of a few of the numbers that should be summed.

By the way...you could eliminate some redundancy in your earlier formula by incorporating the IF "CORPORATE CUSTOMER" component inside the SUMIFS, as that seems to be the only reason for using one column vs. another column in SUMIFS:

=SUM(SUMIFS(DATA!$O:$O,IF(A4="CORPORATE CUSTOMER",DATA!$I:$I,DATA!$J:$J),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)))

You're right on both counts - I changed the values to text and it worked. Now that I know I can use IF statements inside SUMIFS, your other suggestion will work wonderfully, too. Thanks again!

 

[KRice]

Oh...it is correct? After posting, I tried it without success. There is a data mismatch issue of some type. So your column G in the example...is that real-life?...you have a list a true numeric values (not text that appears to be numbers)? You can confirm the data type with the TYPE function. For example, what does =TYPE(G2) return (type this is some unused cell)...a 1 means numeric, 2 is text.

If you select 100 from your drop down data validation list in N1, what does =TYPE(N1) yield? It would be good to confirm this.

 

[KRice]

I wanted to revisit the last issue that developed where your column G of numeric values is tied to some evaluation that depends on the contents of cell N1, and the contents of cell N1 are chosen by the user from a dropdown list. The dropdown list includes both numeric and text values, and this can create some problems, depending on what we want to formula to do for each of the potential selections in N1. In this specific example, if a numeric value is selected in N1, we want to consider only rows in G where G2:G18=N1. There is no issue at all in this case...SUMIFS will handle these scenarios. But if N1="All" (a text value), we want SUMIFS to evaluate as TRUE all corresponding numeric values in G2:G18. If we were dealing with text in G2:G18, we would rely on a wildcard ("*"), but G2:G18="*" is not valid when the G range is numeric...we have a data type mismatch. So we need to work around the problem.

One idea would be to convert the data in column G to text and then use a wildcard for the evaluation, such as TEXT($G$2:$G$18,"0")="*", which would evaluate as an array of TRUES. But SUMIFS does not allow the criterion range (G2:G18) to be operated on within the formula, and importantly, this conversion would cause problems with numeric values chosen for N1, unless they too were converted to text values.

So a different approach is needed, but within the SUMIFS function, the criterion range needs to remain unchanged. Since $G$2:$G$18 are numeric values, we can force all of those values to be accepted as TRUE if, instead of employing a wildcard, we subject them to a logical test that SUMIFS will accept...and an obvious test is something like ">-9.99E307" (arguably, a slightly more robust test would extend the 9's to more smaller place values to hit the absolute negative limit, but that is likely not important). We use a very large negative number, close to the limit of what Excel can handle, such that any numeric value in column G would evaluate as TRUE when subjected to this logical test. In practice, the solution looks like the one shown in O3:P3.

I wanted to call your attention to something I noticed about the data heading above these formulas...something to be aware of. The 1st date shown is April 24, 2024. In earlier examples, dates were shown in mmm-yy format, so any day in April of the year 2024 would appear as Apr-24. The potential problem is that dates in the data table (column J) need to match exactly, so the data table could have dates of April 1, 2024 and April 2, 2024...and neither would be considered a match, even if they were formatted to display as mmm-yy (Apr-24) and the formula heading in O2 (which is 4/24/2024) were also formatted to display as mmm-yy (Apr-24).

I've also shown several variants of a formula using SUMPRODUCT. I often rely on SUMPRODUCT---and use it instead of SUMIF(S)---because it handles arrays well and offers greater flexibility to operate on arrays involved in the logical expressions. However, a different approach is needed for making the OR tests to identify the relative positions of rows in the data table where the Customer and Groups OR criteria are satisfied. I've shown two basic approaches for doing this: one uses matrix multiplication (the MMULT function); the other uses a BYROW LAMBDA function. I also played around with a couple of options for creating the final logical array within SUMPRODUCT...whether to multiply the component arrays directly (with *) or let each stand as a separate argument (separated by commas) passed to the SUMPRODUCT function. Generally, these two options are equivalent, but there are some differences under certain circumstances (that I do not fully understand). One such instance can be demonstrated by changing ROW($G$2:$G$18)^0 to 1 in the O5 formula and choosing "All" in N1. A #VALUE! is produced, while the same modification in the O6 formula delivers the correct answer. Somehow, a single value of 1 is okay when an explicit multiplication operation is performed (the O6 case), but a full array of 1's must be generated when passed as a separate argument (the O5 case). It's safer to simply generate the array of 1's in both cases to avoid this issue. Another advantage of SUMPRODUCT is that more than two sets of OR criteria could appear in the same formula, should that become necessary...this would not be possible in SUMIFS, as mentioned earlier.

MrExcel_20240108.xlsx
	G	H	I	J	K	L	M	N	O	P
1	Co	Corporate Name	Item Group	Month	Forecast		Customer	100
2	100	Springs	Spares	24-Apr	85000		Springs		24-Apr	24-May
3	100	Springs	Comp	24-May	60000		Ball		115500	90400
4	500	Springs	Spares	24-Apr	37500				115500	90400
5	500	Springs	Comp	24-May	25000				115500
6	100	Springs	Spares	24-Apr	24000				115500
7	100	Springs	Comp	24-May	15400
8	500	Springs	Spares	24-Apr	15000
9	500	Ball	Comp	24-May	12000
10	100	Ball	not Spare	24-Apr	10000
11	100	Ball	Comp	24-May	10000		Groups
12	500	Ball	Spares	24-Apr	8400		Spares
13	500	Ball	Comp	24-May	6000		Comp
14	100	Ball	Spares	24-Apr	5000
15	100	Ball	Comp	24-May	5000
16	500	Ball	Spares	24-Apr	2500
17	500	Ball	Comp	24-May	2000
18	100	Ball	Spares	24-Apr	1500
Sheet2

Cell Formulas
Range	Formula
O3:P3	P3	=SUM(SUMIFS($K$2:$K$18,$J$2:$J$18,P2,$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",">-9.99E307",$N$1)))
O4:P4	P4	=SUMPRODUCT($K$2:$K$18,($J$2:$J$18=P2)*MMULT(--($H$2:$H$18=TRANSPOSE($M$2:$M$9)),ROW($M$2:$M$9)^0)*MMULT(--($I$2:$I$18=TRANSPOSE($M$12:$M$19)),ROW($M$12:$M$19)^0)*IF($N$1="All",1,$G$2:$G$18=$N$1) )
O5	O5	=SUMPRODUCT($K$2:$K$18,--($J$2:$J$18=O2),BYROW(--($H$2:$H$18=TRANSPOSE($M$2:$M$9)),LAMBDA(r,SUM(r))),BYROW(--($I$2:$I$18=TRANSPOSE($M$12:$M$19)),LAMBDA(r,SUM(r))),IF($N$1="All",ROW($G$2:$G$18)^0,--($G$2:$G$18=$N$1)) )
O6	O6	=SUMPRODUCT($K$2:$K$18,($J$2:$J$18=O2)*BYROW(--($H$2:$H$18=TRANSPOSE($M$2:$M$9)),LAMBDA(r,SUM(r)))*BYROW(--($I$2:$I$18=TRANSPOSE($M$12:$M$19)),LAMBDA(r,SUM(r)))*IF($N$1="All",ROW($G$2:$G$18)^0,$G$2:$G$18=$N$1) )

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

 

[excelbytes]

I wanted to revisit the last issue that developed where your column G of numeric values is tied to some evaluation that depends on the contents of cell N1, and the contents of cell N1 are chosen by the user from a dropdown list. The dropdown list includes both numeric and text values, and this can create some problems, depending on what we want to formula to do for each of the potential selections in N1. In this specific example, if a numeric value is selected in N1, we want to consider only rows in G where G2:G18=N1. There is no issue at all in this case...SUMIFS will handle these scenarios. But if N1="All" (a text value), we want SUMIFS to evaluate as TRUE all corresponding numeric values in G2:G18. If we were dealing with text in G2:G18, we would rely on a wildcard ("*"), but G2:G18="*" is not valid when the G range is numeric...we have a data type mismatch. So we need to work around the problem.

One idea would be to convert the data in column G to text and then use a wildcard for the evaluation, such as TEXT($G$2:$G$18,"0")="*", which would evaluate as an array of TRUES. But SUMIFS does not allow the criterion range (G2:G18) to be operated on within the formula, and importantly, this conversion would cause problems with numeric values chosen for N1, unless they too were converted to text values.

So a different approach is needed, but within the SUMIFS function, the criterion range needs to remain unchanged. Since $G$2:$G$18 are numeric values, we can force all of those values to be accepted as TRUE if, instead of employing a wildcard, we subject them to a logical test that SUMIFS will accept...and an obvious test is something like ">-9.99E307" (arguably, a slightly more robust test would extend the 9's to more smaller place values to hit the absolute negative limit, but that is likely not important). We use a very large negative number, close to the limit of what Excel can handle, such that any numeric value in column G would evaluate as TRUE when subjected to this logical test. In practice, the solution looks like the one shown in O3:P3.

I wanted to call your attention to something I noticed about the data heading above these formulas...something to be aware of. The 1st date shown is April 24, 2024. In earlier examples, dates were shown in mmm-yy format, so any day in April of the year 2024 would appear as Apr-24. The potential problem is that dates in the data table (column J) need to match exactly, so the data table could have dates of April 1, 2024 and April 2, 2024...and neither would be considered a match, even if they were formatted to display as mmm-yy (Apr-24) and the formula heading in O2 (which is 4/24/2024) were also formatted to display as mmm-yy (Apr-24).

I've also shown several variants of a formula using SUMPRODUCT. I often rely on SUMPRODUCT---and use it instead of SUMIF(S)---because it handles arrays well and offers greater flexibility to operate on arrays involved in the logical expressions. However, a different approach is needed for making the OR tests to identify the relative positions of rows in the data table where the Customer and Groups OR criteria are satisfied. I've shown two basic approaches for doing this: one uses matrix multiplication (the MMULT function); the other uses a BYROW LAMBDA function. I also played around with a couple of options for creating the final logical array within SUMPRODUCT...whether to multiply the component arrays directly (with *) or let each stand as a separate argument (separated by commas) passed to the SUMPRODUCT function. Generally, these two options are equivalent, but there are some differences under certain circumstances (that I do not fully understand). One such instance can be demonstrated by changing ROW($G$2:$G$18)^0 to 1 in the O5 formula and choosing "All" in N1. A #VALUE! is produced, while the same modification in the O6 formula delivers the correct answer. Somehow, a single value of 1 is okay when an explicit multiplication operation is performed (the O6 case), but a full array of 1's must be generated when passed as a separate argument (the O5 case). It's safer to simply generate the array of 1's in both cases to avoid this issue. Another advantage of SUMPRODUCT is that more than two sets of OR criteria could appear in the same formula, should that become necessary...this would not be possible in SUMIFS, as mentioned earlier.

Here is my ultimate formula:
=SUM(SUMIFS('AOP DATA'!$I:$I,IF($A$4="CORPORATE CUSTOMER",'AOP DATA'!$C:$C,'AOP DATA'!$D:$D),IF($B$4="All","*",TRANSPOSE($A$10:$A$14)),'AOP DATA'!$G:$G,F$4,'AOP DATA'!$E:$E,IF($B$3="All","*",$A$17:$A$21),'AOP DATA'!$A:$A,IF($B$2="All",">0",$B$2),'AOP DATA'!$F:$F,IF($B$1="All","*",$B$1)))
I used your suggestion that you used in cell P3 of the sample file and it works great. I can't thank you enough for all your help on this project!

 

[KRice]

You're welcome...I'm happy to help. I see you must not be working with any negative numbers, so the >0 works to identify rows to consider where numeric data are found...and the "*" wildcards are applied only to columns containing text.

 

[excelbytes]

So I keep hitting walls. It turns out that the range of data in one of the worksheets has trailing zeros in the text. Obviously, wrapping that range in a TRIM functions won't work. Will changing all the formulas to SUMPRODUCT allow me to do that?

 

[KRice]

Using the nomenclature in your almost working version of the formula from post #15, which data column are you talking about? I'm assuming the column appears to be mostly text until you get down to some point...at which point, you see 0's? Are those 0's delivered by a formula? Intentionally inserted? I'm curious how the 0's got there.

 

[excelbytes]

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

It's the customer list alternate as highlighted in red above. The drop down list that populates $A$10:$A$14 comes from one source that has no trailing zeros, but the data from which I grab the values has customers that have trailing zeros. Therefore none of the customers match. The only time I get values is what I use "All".

The reality is that this is a 90Mb file and I have 4 similar formula pulling from various data sources in the workbook. In addition to the above, there are these:

=SUM(SUMIFS(Bookings!$W:$W,IF($A$4="CORPORATE CUSTOMER",Bookings!$AB:$AB,Bookings!$AA:$AA),IF($B$4="All","*",TRANSPOSE($A$10:$A$14)),Bookings!$S:$S,F$4,Bookings!$G:$G,IF($B$3="All","*",$A$17:$A$21),Bookings!$D:$D,IF($B$2="All",">0",$B$2),Bookings!$AC:$AC,IF($B$1="All","*",$B$1)))

=SUM(SUMIFS('Open Orders'!$R:$R,IF($A$4="CORPORATE CUSTOMER",'Open Orders'!$AJ:$AJ,'Open Orders'!$AI:$AI),IF($B$4="All","*",TRANSPOSE($A$10:$A$14)),'Open Orders'!$U:$U,F$4,'Open Orders'!$I:$I,IF($B$3="All","*",$A$17:$A$21),'Open Orders'!$A:$A,IF($B$2="All",">0",$B$2),'Open Orders'!$AT:$AT,IF($B$1="All","*",$B$1)))

=SUM(SUMIFS(Shipments!$W:$W,IF($A$4="CORPORATE CUSTOMER",Shipments!$AQ:$AQ,Shipments!$AP:$AP),IF($B$4="All","*",TRANSPOSE($A$10:$A$14)),Shipments!$U:$U,F$4,Shipments!$Q:$Q,IF($B$3="All","*",$A$17:$A$21),Shipments!$D:$D,IF($B$2="All",">0",$B$2),Shipments!$AY:$AY,IF($B$1="All","*",$B$1)))

The lists in Red all have similar issues.

 

[KRice]

Can you give an example of what a trailing 0 looks like. Are you saying that instead of “Bob”, you might see “Bob0”?