SUMIFS Multiple Criteria in Multiple Columns

[john-paul]

Hi,

I have a table from A1 with two columns containing multiple criteria, and column C with amounts.

Criteria ranges:
Region Selected input range: E2 (North) E3 (South)
Division Selected input range: F2 (Manufacturing) F3 (Advertising)

Hoping for a sumifs formula that references the ranges in E2:E3 and F2:F3 to sum North or South where Manufacturing or Advertising so it should sum to 400.


Thanks, John

Region	Division	$
North	Manufacturing	100
North	Advertising	100
South	Manufacturing	100
South	Advertising	100
East	Manufacturing	100
East	Advertising	100
North	Finance	100
South	Finance	100

 

[jasonb75]

You can do it like this, note that the second array uses a semicolon delimiter instead of the usual comma.

Book1
	A	B	C	D	E
1	Region	Division	$
2	North	Manufacturing	100		400
3	North	Advertising	100
4	South	Manufacturing	100
5	South	Advertising	100
6	East	Manufacturing	100
7	East	Advertising	100
8	North	Finance	100
9	South	Finance	100
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUM(SUMIFS($C$2:$C$9,$A$2:$A$9,{"North","South"},$B$2:$B$9,{"advertising";"manufacturing"}))

 

[john-paul]

Thanks jasonb75.

Is it possible to use the ranges E2:E3 and F2:F3 in the formula for when the input in these ranges change?

 

[jasonb75]

Sorry, I think that I must have read that part of your question with my eyes closed

If you're using excel 2019 (or older) then you will need to array confirm the formula with Ctrl Shift Enter.

Book1
	A	B	C	D	E	F
1	Region	Division	$
2	North	Manufacturing	100		North	Manufacturing
3	North	Advertising	100		South	Advertising
4	South	Manufacturing	100
5	South	Advertising	100		400
6	East	Manufacturing	100
7	East	Advertising	100
8	North	Finance	100
9	South	Finance	100
Sheet1

Cell Formulas
Range	Formula
E5	E5	=SUM(SUMIFS($C$2:$C$9,$A$2:$A$9,$E$2:$E$3,$B$2:$B$9,TRANSPOSE($F$2:$F$3)))

 

[john-paul]

Thanks, that works perfectly.
Didn't realise I needed TRANSPOSE, or to use a semicolon delimiter in the earlier version.

 

[jasonb75]

When you use sumifs in this way, one of the array criteria must be horizontal (comma delimiter) and the other vertical (semicolon delimiter).
With the criteria in cells, using TRANSPOSE changes the direction of that array (it doesn't matter which one).

If you have both arrays in the same orientation then the criteria are paired in their rows, using your example it would only pick up North Manufacturing and South Advertising, the other pairings would not be included.

If you needed more than 2 sets of multiple criteria columns then this method would not work. There are a few ways of doing it with more criteria depending on the requirements and the version of excel.

 

[john-paul]

Thank you not just for the formula, but also how and why it works.