Sumifs with multiple criteria

[ellison]

Hi, try as I might, I can't seem to work out how to add extra criteria when I'm using the SUMIF function.

On the left is the raw data (which will sometimes contain blanks), on the right is the SUMIF function (& some blank cells where I'm hoping the SUMIFS for the multiple criteria may go! )

Header-1	Header-2	Header-3	Header-4	Amt		SUMIF for 1 criteria in Header-2: #=SUMIF($B$2:$E$10,B2,$E$2:$E$10)	SUMIF for 2 criteria in Header-2 and Header-3	SUMIF for 3 criteria in Header-2 / Header-3 / Header-4
2	South	alpha	black	100		950
3	North	echo		50		75
4	East			75		75
5	North	echo	red	25		75
6	East					75
7	North	beta	red			75
8	West	charlie	black	250		250
9	South	alpha	black	350		950
10	South	alpha	purple	500		950

Huge thanks for taking a look!

 

[Phuoc]

Try this

Book1
	A	B	C	D	E	F	G	H	I
1	Header-1	Header-2	Header-3	Header-4	Amt		SUMIF for 1 criteria in Header-2: #=SUMIF($B$2:$E$10,B2,$E$2:$E$10)	SUMIF for 2 criteria in Header-2 and Header-3	SUMIF for 3 criteria in Header-2 / Header-3 / Header-4
2	2	South	alpha	black	100		950	950	450
3	3	North	echo		50		75	75	0
4	4	East			75		75	0	0
5	5	North	echo	red	25		75	75	25
6	6	East					75	0	0
7	7	North	beta	red			75	0	0
8	8	West	charlie	black	250		250	250	250
9	9	South	alpha	black	350		950	950	450
10	10	South	alpha	purple	500		950	950	500
Sheet1

Cell Formulas
Range	Formula
G2:G10	G2	=SUMIFS($E$2:$E$10,$B$2:$B$10,B2)
H2:H10	H2	=SUMIFS($E$2:$E$10,$B$2:$B$10,B2,$C$2:$C$10,C2)
I2:I10	I2	=SUMIFS($E$2:$E$10,$B$2:$B$10,B2,$C$2:$C$10,C2,$D$2:$D$10,D2)

 

[ellison]

That works beautifully, huge thanks !
Also gives me a chance to get my teeth into how that formula works - with a working example, phew
All the best