Hello,
Does anyone know on how to combine SUMIF with INDEX MATCH. I have an issue whereby my lookup only identify first result that is in Sample C2. My intention that my formula will sum all the figure in column C i.e 700,000. My lookup value need to be maintain on Account number & Item no.
Sample
<tbody>
</tbody>
My result using below formula.
=INDEX(Sample!A2:C4,MATCH(A2,Sample!B1:B4,0),MATCH(B2,Sample!C1:C2,0))
<tbody>
</tbody>
Intended result
<tbody>
</tbody>
<tbody>
</tbody>
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">ABC1Account NumberItemAmount221701001700,000
Does anyone know on how to combine SUMIF with INDEX MATCH. I have an issue whereby my lookup only identify first result that is in Sample C2. My intention that my formula will sum all the figure in column C i.e 700,000. My lookup value need to be maintain on Account number & Item no.
Sample
A | B | C | D | |
1 | Co Code | Account Number | 1 | 2 |
2 | 9099 | 2170100 | 659,000 | 0 |
3 | 9099 | 2170100 | 2,300 | 700 |
4 | 9099 | 2170100 | 38,700 | 56 |
<tbody>
</tbody>
My result using below formula.
=INDEX(Sample!A2:C4,MATCH(A2,Sample!B1:B4,0),MATCH(B2,Sample!C1:C2,0))
A | B | C | |
1 | Account Number | Item | Amount |
2 | 2170100 | 1 | 659,000 |
<tbody>
</tbody>
Intended result
A | B | C | |
1 | Account Number | Item | Amount |
2 | 2170100 | 1 | 700,000 |
<tbody>
</tbody>
3 | 2170100 | 2 | 756 |
<tbody>
</tbody>
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">ABC1Account NumberItemAmount221701001700,000
Last edited: