Sum index match multiple row multiple column - referring to an old post link inside

[shakethingsup]

Sum INDEX-MATCH Across Multiple Columns with the Same Heading

Hello - how - wondering if someone knows how to modify this formula to account for multiple rows could be a match? Same Data but now multiple rows and multiple columns match?

instead of:
=SUMIFS(INDEX(B:G,MATCH(J1,A:A,0),0),INDEX(B:G,1,0),K1)

I thought it would be
=SUMIFS(INDEX(B:G,,),INDEX(A:A,0,1),J1,INDEX(B:G,1,0),K1)

I tried each part of the formula separately, including smaller rangers and they seemed to work, why doesn't it work together?

 

[Alex Blakenburg]

Since you have MS 365, this should work for you.

20221212 Filter 2 dimensions shakethingsup.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Yes	Yes	Yes	No	No	No			A	No	28
2	A	2	4	6	8	2	4
3	B	6	8	2	4	6	8
4	C	2	4	6	8	2	4
5	D	6	8	2	4	6	8
6	A	2	4	6	8	2	4
7	B	6	8	2	4	6	8
8	C	2	4	6	8	2	4
9	D	6	8	2	4	6	8
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUM( FILTER( FILTER( $A$1:$G$9, $A$1:$G$1 = "No" ), ($A$1:$A$9 = J1) ) )

 

[kevin9999]

Or if you have to share it with colleagues who have older versions...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Yes	Yes	Yes	No	No	No			A	No	28
2	A	2	4	6	8	2	4
3	B	6	8	2	4	6	8
4	C	2	4	6	8	2	4
5	D	6	8	2	4	6	8
6	A	2	4	6	8	2	4
7	B	6	8	2	4	6	8
8	C	2	4	6	8	2	4
9	D	6	8	2	4	6	8
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUMPRODUCT(($A$2:$A$9=$J$1)*($B$1:$G$1=$K$1),$B$2:$G$9)

 

[shakethingsup]

Since you have MS 365, this should work for you.

20221212 Filter 2 dimensions shakethingsup.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Yes	Yes	Yes	No	No	No			A	No	28
2	A	2	4	6	8	2	4
3	B	6	8	2	4	6	8
4	C	2	4	6	8	2	4
5	D	6	8	2	4	6	8
6	A	2	4	6	8	2	4
7	B	6	8	2	4	6	8
8	C	2	4	6	8	2	4
9	D	6	8	2	4	6	8
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUM( FILTER( FILTER( $A$1:$G$9, $A$1:$G$1 = "No" ), ($A$1:$A$9 = J1) ) )

both worked. Didn't know about Filter. Thank you! learned something.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.