SUMIFS with INDEX MATCH

[atearth]

Hi all,

Please see the attached image

Within cell C11 how do I use the SUMIFS INDEX MATCH to get the results I'm after?

 

[snjpverma]

It seems the answer to this question was already given in your previous post on this forum.

 

[atearth]

For some reason the formula gives the wrong answer.

Any help?

C11 should be 40, but it is showing 10.

 

[fjns]

Hi, see the linked file for a possible solution...
Wrong cells visible on Google Drive, because Google Drive does not know the FILTER function. The formula works correctly with your Office 365.

The formula used in the table:
=IFERROR(SUM(FILTER(FILTER($C$3:$N$5,$B$3:$B$5=$B11),$C$2:$N$2=C$10)),0)

WBS.xlsx

 

[kevin9999]

Another possibility?

green.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	WBS	A	A	A	A	B	B	B	B	C	C	C	C
3	100	10	10	10	10	20	20	20	20	30	30	30	30
4	101	20	20	20	20							20	20
5	102	30	30	30	30		50	50		10
6
7
8
9
10	WBS	A	B	C
11	100	40	80	120
12	101	80	0	40
13	102	120	100	10
Sheet5

Cell Formulas
Range	Formula
C11:E13	C11	=SUMPRODUCT(($C$2:$N$2=C$10)*($B$3:$B$5=$B11),$C$3:$N$5)