aboydprice
New Member
- Joined
- Nov 6, 2019
- Messages
- 1
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))
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>
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))
B | C | D | E | F | G | H | I | J | K | L | M | |
2 | OC_840 | OC_100 | OC_861 | OC_TECH | OC_TECH | OC_840 | OC_100 | OC_861 | OC_TECH | |||
3 | Cash | 1 | 4 | 7 | 10 | 13 | Cash | 1 | 4 | 7 | 10 | |
4 | RR | 2 | 5 | 8 | 11 | 14 | RR | 2 | 5 | 8 | 11 | |
5 | TA | 3 | 6 | 9 | 12 | 15 | TA | 3 | 6 | 9 | 12 |
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>