Question re: SUMIFS and Arrays

[masouder]

I am using the SUMIFS functions function with multiple array criteria and have a question about using semi-colons versus commas in the arrays. The following formula using commas in the first criteria incorrectly returns zero:

=SUM(SUMIFS(RevenueYearToDateRevenues,RevenueFund,{"222","26*","27*"},RevenueObjectCode,{"4010*","4230*"}))

The following formula using semi-colons in the first criteria returns the correct result:

=SUM(SUMIFS(RevenueYearToDateRevenues,RevenueFund,{"222";"26*";"27*"},RevenueObjectCode,{"4010*","4230*"}))

Can anyone explain why semi-colons work and commas do not? Also, how would I handle this if there was a third criteria? Lastly, is there a better formula to use?

 

[Fluff]

When using two sets of multiple criteria, one must be horizontal (using commas) & one must be vertical (using semi-colons).
You can only have two sets of multiple criteria.

 

[Eric W]

When you create an array constant with commas, it is a horizontal array. When you create an array constant with semicolons, it is a vertical array. The process Excel uses internally to combine the arrays is different based on the type of arrays. For example, if you have

=SUM(SUMIFS(rng1,rng2,{"a","b"},rng3,{1,2}))

then Excel calculates

SUMIFS(rng1,rng2,"a",rng3,1)

SUMIFS(rng1,rng2,"b",rng3,2)

and sums them. Since the arrays are in the same orientation, it assumes that the elements of each array match up with each other. So you'd miss the a,2 and b,1 combinations. If you use a comma on one of the array constants, and the semicolon on the other, then Excel creates an internal 2-D array with every combination in the 2-D array, and sums that.

When you have 2 arrays with the same orientation of different sizes, even stranger things can happen.

So no, you can't use the SUM(SUMIFS construct with more than 2 array constants. Excel only handles 2-D arrays. If you want to use more than 2 ranges with multiple values, you could do something like:

=SUMPRODUCT(sumrange,--ISNUMBER(MATCH(range1,{"a","b","c"},0)),--ISNUMBER(MATCH(range2,{1,2},0)),--ISNUMBER(MATCH(range3,{"Amy","Bob","Cal","Deb"},0)))

Using actual ranges on your workbook for the array constants would probably be neater though, and allow for easier changes.

 

[masouder]

Thanks, Eric!

 

[masouder]

Fluff, thank you too.

 

[masouder]

Eric, actually I spoke too soon. I tried the formula and it did not appear to work. Can you tell me what is wrong with the below formula?

=SUMPRODUCT(RevenueYearToDateRevenues,--ISNUMBER(MATCH(RevenueFund,{"222","26*","27*"},0)),--ISNUMBER(MATCH(RevenueObjectCode,{"4010*","4230*"},0)))

 

[Eric W]

It's the wildcards. Wildcards don't work with MATCH. You might have to break up each condition separately, and do something like this:

Book1
	A	B	C	D	E
1
2
3	1	222	40101		32
4	2	261	4230ab
5	3	777	40102
6	4	262	40103
7	5	111	4230x
8	6	277	423022
9	7	ab	xx
10	8
11	9	222	42305
12	10	266	42305
13
Sheet1

Cell Formulas
Range	Formula
E3	E3	=SUMPRODUCT(RevenueYearToDateRevenues,SIGN((RevenueFund=222)+(LEFT(RevenueFund,2)="26")+(LEFT(RevenueFund,2)="27")),SIGN((LEFT(RevenueObjectCode,4)="4010")+(LEFT(RevenueObjectCode,4)="4230")))

Named Ranges
Name	Refers To	Cells
RevenueFund	=Sheet1!$B$3:$B$12	E3
RevenueObjectCode	=Sheet1!$C$3:$C$12	E3
RevenueYearToDateRevenues	=Sheet1!$A$3:$A$12	E3

It's worth mentioning that the FILTER function in newer versions of Excel might make it a bit easier, but you'd still have to list each condition separately. There might be some ways to simplify this a bit depending on the characteristics of your data (for example, are the RevenueFund values all 3 digits?), but I don't see any great way to simplify it. If you only have two columns to check, the SUM(SUMIFS( with 2 array constants is probably the cleanest. With more than two columns, you have to do something like this example.

 

[masouder]

Thanks, Eric.