SUMIFS across a range

[reacon84]

Hi, I posted a question recently which this excellent community very kindly helped me with. However, the spreadsheet has evolved a little since then and I'm hopefully looking for another solution.

What I'd like to do is have a formula that would 'find' all Jan-21 Type A amounts and sum the corresponding amounts in H2:L6. Then do the same for Jan-21 Type B, Feb-21 Type A and Feb-21 Type B.

The blue box shows the outcome I'm looking for but I'm a bit stuck with the formula. I've tried using a SUMIF range but just get a #VALUE.

Thanks for any help in advance.

 

[Habtest]

Hi there, try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Type	1	2	3	4	5		1	2	3	4	5
2	Type A	21-Jan						10	30	40	10	20
3	Type B		21-Feb	21-Jan				10	15	25	10	35
4	Type A				21-Feb	21-Jan		10	30	40	10	50
5	Type A				21-Feb			30	20	10	15	25
6	Type B			21-Jan				40	25	15	30	40
7
8						21-Jan	21-Feb
9					Type A	60	25
10					Type B	40	15
Sheet3

Cell Formulas
Range	Formula
F9:G10	F9	=SUMPRODUCT(($A$2:$A$6=$E9)*($B$2:$F$6=F$8)*($H$2:$L$6))

Also, it'd be easier to help out if you could paste sample data by XL2BB next time.

 

[hrayani]

Try this in F9

=SUMIFS($H$2:$H$6,$B$2:$B$6,F$8,$A$2:$A$6,$E9)+SUMIFS($I$2:$I$6,$C$2:$C$6,F$8,$A$2:$A$6,$E9)+SUMIFS($J$2:$J$6,$D$2:$D$6,F$8,$A$2:$A$6,$E9)+SUMIFS($K$2:$K$6,$E$2:$E$6,F$8,$A$2:$A$6,$E9)+SUMIFS($L$2:$L$6,$F$2:$F$6,F$8,$A$2:$A$6,$E9)

I am sure there must be a simpler way of doing this - but this is what I know

 

[reacon84]

Thank you so much for the replies! Always a great help!

Have a great day!

 

[Habtest]

Thank you so much for the replies! Always a great help!

Have a great day!

No problem, and you too. Thanks for the follow-up.

 

[hrayani]

Thank you so much for the replies! Always a great help!

Have a great day!

You are welcome