INDEX MATCH two criteria with SUMIF

[vostroxe]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Co Code[/TD]
[TD]Account Number[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9099[/TD]
[TD]2170100[/TD]
[TD]659,000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9099[/TD]
[TD]2170100[/TD]
[TD]2,300[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9099[/TD]
[TD]2170100[/TD]
[TD]38,700[/TD]
[TD]56[/TD]
[/TR]
</tbody>[/TABLE]

My result using below formula.

=INDEX(Sample!A2:C4,MATCH(A2,Sample!B1:B4,0),MATCH(B2,Sample!C1:C2,0))

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Account Number[/TD]
[TD]Item[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2170100[/TD]
[TD]1[/TD]
[TD]659,000[/TD]
[/TR]
</tbody>[/TABLE]


Intended result


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Account Number[/TD]
[TD]Item[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2170100[/TD]
[TD]1[/TD]
[TD]700,000[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]3[/TD]
[TD]2170100[/TD]
[TD]2[/TD]
[TD]756[/TD]
[/TR]
</tbody>[/TABLE]


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">ABC1Account NumberItemAmount221701001700,000

 

[Fluff]

How about

Book1
	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
5
6
11
12
13
14	Account Number	Item	Amount
15	2170100	1	700,000
16	2170100	2	756
Sheet4

Cell Formulas
Range	Formula
C15	=SUMIF($B$2:$B$4,A15,INDEX($C$2:$D$4,,MATCH(B15,$C$1:$D$1,0)))

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Co Code​

[/td][td]

Account Number​

[/td][td]

1​

[/td][td]

2​

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

Account Number​

[/td][td]

Item​

[/td][td]

Amount​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

9099​

[/td][td]

2170100​

[/td][td]

659000​

[/td][td]

0​

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

2170100​

[/td][td]

1​

[/td][td]

700000​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

9099​

[/td][td]

2170100​

[/td][td]

2300​

[/td][td]

700​

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

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

9099​

[/td][td]

2170100​

[/td][td]

38700​

[/td][td]

56​

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

Formula in H2
=SUMIF($B:$B,F2,INDEX($A:$D,0,MATCH(G2,$A$1:$D$1,0)))

M.

 

[vostroxe]

Thank you mate. It works!!

 

[Fluff]

Glad we could help & thanks for the feedback