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?

 

[excelbytes]

I have no idea why I said trailing zeros. I meant trailing spaces. I guess this project is getting the best of me

 

[KRice]

OK...yes. That's a problem. SUMIFS requires a range, not an array, for the criterion ranges...so you can't easily clean up those trailing spaces from within SUMIFS using TRIM. I'd recommend abandoning SUMIFS and use SUMPRODUCT, and then, because you seem to be using the same formula template for multiple computations, you might consider placing the SUMPRODUCT inside a LET function and then do some preliminary set-up work to make the formula a little easier to understand and edit. Here is an example, beginning with a mock-up AOP DATA sheet snippet (I've added some trailing spaces to many of the column D entries):

MrExcel_20240108.xlsx
	A	B	C	D	E	F	G	H	I
1	Co		Corporate Name	Not Corporate Name	Item Group	Account Manager	Month		Forecast
2	100		Springs	z	Spares	3	Apr-24		1
3	100		Ball	Crown	Comp	3	Apr-24		2
4	100		Crown	Crown	Spares	b	May-24		3
5	100		Ball	Crown	Comp	Z	Apr-24		4
6	100		Ball	Crown	Not Comp	b	Apr-24		5
7	100		Not Crown	Crown	Spares	b	Apr-24		6
8	500		Crown	Crown	Comp	b	Apr-24		7
9	100		Crown	Ball	Rejects	b	Apr-24		8
10	500		Crown	z	Comp	b	Apr-24		9
11	100		Crown	Ball	Rejects	b	Apr-24		10
12	500		Ball	z	Comp	b	Apr-24		11
13	500		Crown	z	Comp	b	Apr-24		12
14	100		Ball	Ball	Reinspections	b	Apr-24		13
15	100		Crown	Ball	Rejects	b	Apr-24		14
16	500		Crown	z	Comp	b	Apr-24		15
17	500		Crown	Ball	Spares	b	Apr-24		16
18	100		Springs	z	Comp. Spares	b	Apr-24		17
AOP DATA

The set-up work involves specifying the entire data range, the Customer items list range, the Groups list range, and then confirming which column indexes correspond to the named variables (co, cusc, cusd, igrp, acctmgr, dt, and fcast (change these as desired to something that makes sense to you...change throughout formula, but avoid using names that might be interpreted as a cell address, such as dt3). This approach allows you to clean-up any data should you discover an issue with it (see how TRIM is applied to cusd to eliminate the trailing spaces). Then two more variables are defined: cus is the Customer array that should be used, depending on the contents of cell A4; and allrows is a single column array that is swapped in and used when all rows of some array need to be taken. Then the SUMPRODUCT formula is written with some internal line breaks to more clearly see the different components, each separated by commas. Where necessary, any TRUE/FALSE arrays are coerced to 1's and 0's with the double unary (--) operator.

MrExcel_20240108.xlsx
	A	B				F
1	Acct Mgr Data F crit: All? (T *),(F B1)	b
2	Data A crit: All? (T >0),(F B2)	100
3	Data E Group crit: All? (T *), (F A17:A21 ORs )	Any Group Items Shown Below
4	Not Corporate Customer	Any Customer Items Shown Below				4/1/2024
5	Corporate Customer? (T Data C), (F Data D)	Data C/D Customer crit: All? (T *),(F A10:A14 ORs)				Data G crit
6
7	51	Result summing Data I				List of Acct Mgrs
8						All
9	Customer					3
10	Ball	Data C/D crit				b
11	Crown					Z
12	Springs
13
14
15
16	Groups
17	Comp	Data E crit
18	Spares
19	Rejects
20	Reinspections
21
Sheet9

Cell Formulas
Range	Formula
A7	A7	=LET(data,'AOP DATA'!A1:I23,cusitems,A10:A14,grpitems,A17:A21, co,INDEX(data,,1),cusc,INDEX(data,,3),cusd,TRIM(INDEX(data,,4)),igrp,INDEX(data,,5),acctmgr,INDEX(data,,6),dt,INDEX(data,,7),fcast,INDEX(data,,9), cus,IF($A$4="CORPORATE CUSTOMER",cusc,cusd), allrows,SEQUENCE(ROWS(data),,1,0), SUMPRODUCT(fcast, --(dt=F$4), IF($B$1="All",allrows,--(acctmgr=$B$1)), IF($B$2="All",allrows,--(co=$B$2)), IF($B$3="All",allrows,BYROW(--(igrp=TRANSPOSE(grpitems)),LAMBDA(r,SUM(r)))), IF($B$4="All",allrows,BYROW(--(cus=TRANSPOSE(cusitems)),LAMBDA(r,SUM(r))))) )
F8:F11	F8	=VSTACK("All",SORT(UNIQUE('AOP DATA'!F2:F18)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A4	List	Corporate Customer,Not Corporate Customer
B1	List	=$F$8#
B2	List	100,500,All
B3	List	All, Any Group Items Shown Below
B4	List	All, Any Customer Items Shown Below

 

[KRice]

I should have mentioned...you said the file is very large, and I'm not sure how long your data source tables are, but you might want to consider two options:

1. either convert the data source tables to true Excel tables and then rewrite the formulas using structured references (and that will ensure that the entirety of each column in the table is captured). But by recasting the formula using the LET function, there is very little that needs to be done in terms of structured references, as only the initial table is specified and assigned to a named variable (at which point it is considered an array), and the formula breaks apart that large array into single column arrays assigned to more meaningful named variables used later in the formula.
2. leave the data source tables as ranges (not official tables) and revise the formula I posted to specify an overly large range (but not full columns because of inefficiencies) and then use some column in the range reference that is fully populated so that the specified source range can be dynamically trimmed down to include only the rows that are needed. Replace the first row of the formula with this:

   LET(source,'AOP DATA'!A1:I9999,cusitems,A10:A14,grpitems,A17:A21,data,DROP(TAKE(source,COUNTA(INDEX(source,,1))),1),

   where the overly large range covers rows 1:9999 and is assigned to a new variable called source, and then the 1st column---INDEX(source,,1)---is used by COUNTA to determine the number of rows containing something....and then DROP/TAKE are used to eliminate the lower empty rows and the uppermost header row...and this trimmed down source array is assigned to the variable data, which is used in the remainder of the formula.

So for the latter approach, the full formula looks like this:

=LET(source,'AOP DATA'!A1:I9999,cusitems,A10:A14,grpitems,A17:A21,data,DROP(TAKE(source,COUNTA(INDEX(source,,1))),1),
co,INDEX(data,,1),cusc,INDEX(data,,3),cusd,TRIM(INDEX(data,,4)),igrp,INDEX(data,,5),acctmgr,INDEX(data,,6),dt,INDEX(data,,7),fcast,INDEX(data,,9),
cus,IF($A$4="CORPORATE CUSTOMER",cusc,cusd), allrows,SEQUENCE(ROWS(data),,1,0),
SUMPRODUCT(fcast, --(dt=F$4),
IF($B$1="All",allrows,--(acctmgr=$B$1)),
IF($B$2="All",allrows,--(co=$B$2)),
IF($B$3="All",allrows,BYROW(--(igrp=TRANSPOSE(grpitems)),LAMBDA(r,SUM(r)))),
IF($B$4="All",allrows,BYROW(--(cus=TRANSPOSE(cusitems)),LAMBDA(r,SUM(r)))))  )

 

[excelbytes]

As much fun as doing all that sounds, I think our best approach is fix the source data and have the trailing spaces removed. I will study your examples to better understand your suggestion for future use. Thanks again!

 

[KRice]

I agree...the best practice is to clean up the data when it is first generated, but that's not always possible or practical. Without doing that, a potential issue is that you may not even be aware that there is a problem lurking in the source data that violates some assumptions used by the formula. Fortunately, you identified the trailing spaces in one column, and the formula can be changed to clean it up before it is used in the matching component...but does the same problem exist in other columns? Still, the SUMPRODUCT version offers some advantages over SUMIFS in that you can assign the entire data table to the variable called "source" in the formula in just one place, and the logical matching components of the formula then rely on criterion arrays generated within. SUMIFS, on the other hand, will not accept these criterion arrays, and instead will require manually editing the formula to revise each criterion range reference (i.e., point to the correct worksheet and column). If your various data source tables have the same structure and you are using the same match criteria locations (same columns in the data tables), there would be very little effort involved in adapting the SUMPRODUCT formula for the different sums described. The formula in post #23 is a recast version using SUMPRODUCT that duplicates the functionality of those you included in posts #15 and #19.

Could you answer the question I asked about forming a dynamic range? Generally, full column references are a bad idea, but some functions handle them okay. SUMIFS is one of those functions; it internally recognizes the lowest extent of data and ceases computations below that point, so processing efficiency is not adversely affected. Other functions, such as SUMPRODUCT, would waste computational time operating on all million plus rows of the worksheet, even if the lowest data extent was row 200. As easy way to create the dynamically formed data array that captures all of the source data relies on a column that is fully populated (no blanks), and then the COUNTA function can be used. Is there such a column in the source data tables? If not, then a different approach is needed to find the lowest row of data in the source tables,

 

[excelbytes]

My plan is convert all the data ranges to tables then I can easily reference the table name and column and that will dynamically adjust as the data changes.

 

[KRice]

Okay, official tables are beneficial here. Still, there is less formula maintenance with SUMPRODUCT. A structured reference can be used to assign just the data in the table to the data variable, like this:

=LET(data,tblAOP[#Data],cusitems,A10:A14,grpitems,A17:A21,
co,INDEX(data,,1),cusc,INDEX(data,,3),cusd,TRIM(INDEX(data,,4)),igrp,INDEX(data,,5),acctmgr,INDEX(data,,6),dt,INDEX(data,,7),fcast,INDEX(data,,9),
cus,IF($A$4="CORPORATE CUSTOMER",cusc,cusd), allrows,SEQUENCE(ROWS(data),,1,0),
SUMPRODUCT(fcast, --(dt=F$4),
IF($B$1="All",allrows,--(acctmgr=$B$1)),
IF($B$2="All",allrows,--(co=$B$2)),
IF($B$3="All",allrows,BYROW(--(igrp=TRANSPOSE(grpitems)),LAMBDA(r,SUM(r)))),
IF($B$4="All",allrows,BYROW(--(cus=TRANSPOSE(cusitems)),LAMBDA(r,SUM(r))))) )

If all of your other data tables use the same column order/content type:
co,INDEX(data,,1), cusc,INDEX(data,,3), cusd,TRIM(INDEX(data,,4)), igrp,INDEX(data,,5), acctmgr,INDEX(data,,6), dt,INDEX(data,,7), fcast,INDEX(data,,9),
...and you use the same ranges for the Customer and Group matching OR conditions: cusitems,A10:A14, grpitems,A17:A21,
...and you use the same matching term locations to form the query: B1, B2, B3, B4, A4, F4
...then there would be only one edit (shown in red) to direct the formula to a different data table. There is nothing wrong with SUMIFS, assuming you can resolve the trailing space issues with the source data, but each table/column reference in the SUMIFS function will need to be changed.