Sum INDEX-MATCH Across Multiple Columns with the Same Heading

[Kozzahkstan]

Hello,

I am attempting to sum the returns of an INDEX-MATCH formula across multiple columns. I am able to return the first value found, but there are multiple columns with the same heading that I am attempting to SUM. Simple example below:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[/TR]
</tbody>[/TABLE]

I am looking to SUM the values with a column heading of "No" in Row C. The formula I have now is:

=SUMIF('Sheet2'A:A,'Sheet1'A:A,INDEX('Sheet2'E:G,0,MATCH('Sheet1'A3,'Sheet2'E2:G5,0)))

Where Sheet1 has the criteria for the data pull and Sheet2 is above. The 'Sheet1'A3 reference in the MATCH portion of the formula would be identifying the "No" value to search. The formula is returning a value of 8 and I am looking to SUM 8,2,4 from row C in columns E,F,G to return a value of 14.

Any help would be much appreciated!

 

[Aladin Akyurek]

Let A:G house the data.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td] [/td][td]

Yes​

[/td][td]

Yes​

[/td][td]

Yes​

[/td][td]

No​

[/td][td]

No​

[/td][td]

No​

[/td][td][/td][td][/td][td]

A​

[/td][td]

No​

[/td][td]

14​

[/td][/tr]


[tr][td]

2​

[/td][td] A[/td][td]

2​

[/td][td]

4​

[/td][td]

6​

[/td][td]

8​

[/td][td]

2​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

3​

[/td][td] B[/td][td]

6​

[/td][td]

8​

[/td][td]

2​

[/td][td]

4​

[/td][td]

6​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td] C[/td][td]

2​

[/td][td]

4​

[/td][td]

6​

[/td][td]

8​

[/td][td]

2​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td] D[/td][td]

6​

[/td][td]

8​

[/td][td]

2​

[/td][td]

4​

[/td][td]

6​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


J1: A

K1: No

In L1 enter:

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

 

[Kozzahkstan]

Thank you! Worked beautifully! Had to adjust the row identifier in the second INDEX formula since my reference value was in row 4, but worked perfectly!

 

[Aladin Akyurek]

Thank you! Worked beautifully! Had to adjust the row identifier in the second INDEX formula since my reference value was in row 4, but worked perfectly!

You are welcome.

 

[Mike Szczesny]

Can you use something like this? Hope this helps.

Excel 2012
	A	B	C	D	E	F	G
1		yes	yes	yes	no	no	no
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
7	c	14
8	no
Sheet1

Cell Formulas
Range	Formula
B7	{=SUM(IF($A$2:$A$5=$A$7,IF($B$1:$G$1=$A$8,$B$2:$G$5)))}
Press CTRL+SHIFT+ENTER to enter array formulas.