Sum Multiple Columns Index Match Matrix

[aboydprice]

How can I write a formula that will sum the value from multiple columns using an index match matrix based approach?

The formula below will work when there is only one column that has the "OC_TECH" value, but I want to sum both values if "OC_TECH" occurs as a column header more than once.

for example the value in cell M4 should be 23, and M4 should be 25

=INDEX($C$4:$G$6,MATCH($I4,$B$4:$B$6,0),MATCH(M$3,$C$3:$G$3,0))


[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"]B[/TD]
[TD="class: xl67, width: 64"]C[/TD]
[TD="class: xl67, width: 64"]D[/TD]
[TD="class: xl67, width: 64"]E[/TD]
[TD="class: xl67, width: 64"]F[/TD]
[TD="class: xl67, width: 64"]G[/TD]
[TD="class: xl67, width: 64"]H[/TD]
[TD="class: xl67, width: 64"]I[/TD]
[TD="class: xl67, width: 64"]J[/TD]
[TD="class: xl67, width: 64"]K[/TD]
[TD="class: xl67, width: 64"]L[/TD]
[TD="class: xl67, width: 64"]M[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"]OC_840[/TD]
[TD="class: xl65"]OC_100[/TD]
[TD="class: xl65"]OC_861[/TD]
[TD="class: xl65"]OC_TECH[/TD]
[TD="class: xl65"]OC_TECH[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"]OC_840[/TD]
[TD="class: xl65"]OC_100[/TD]
[TD="class: xl65"]OC_861[/TD]
[TD="class: xl65"]OC_TECH[/TD]
[/TR]
[TR]
[TD="class: xl67"]3[/TD]
[TD="class: xl66"]Cash[/TD]
[TD="class: xl66, align: right"]1[/TD]
[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl66, align: right"]7[/TD]
[TD="class: xl66, align: right"]10[/TD]
[TD="class: xl66, align: right"]13[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]Cash[/TD]
[TD="class: xl66, align: right"]1[/TD]
[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl66, align: right"]7[/TD]
[TD="class: xl66, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl67"]4[/TD]
[TD="class: xl66"]RR[/TD]
[TD="class: xl66, align: right"]2[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD="class: xl66, align: right"]8[/TD]
[TD="class: xl66, align: right"]11[/TD]
[TD="class: xl66, align: right"]14[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]RR[/TD]
[TD="class: xl66, align: right"]2[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD="class: xl66, align: right"]8[/TD]
[TD="class: xl66, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl67"]5[/TD]
[TD="class: xl66"]TA[/TD]
[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl66, align: right"]6[/TD]
[TD="class: xl66, align: right"]9[/TD]
[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl66, align: right"]15[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]TA[/TD]
[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl66, align: right"]6[/TD]
[TD="class: xl66, align: right"]9[/TD]
[TD="class: xl66, align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Welcome to the forum.

You might be better off just multiplying arrays and summing them. So put this formula in J3 and copy across then down.

[FONT=Verdana]=SUMPRODUCT((J$2=$C$2:$G$2)*($I3=$B$3:$B$5)*$C$3:$G$5)[/FONT]