Index Match sum if with multiple criteria

[nikhil0311]

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Scenario 1 (ID & LTM/PLTM				Scenario 2 (ID & Region & LTM/PLTM)				Scenario 3 (ID & Country & LTM/PLTM)
2	ID	12345
3
4	Product	LTM	PLTM		Region	LTM	PLTM		Country	LTM	PLTM
5	Core cash	$ 80	$ 110		EMEA	$ 190	$ 300		India	$ 280	$ 283
6	Liquidity	$ 29	$ 89		APAC	$ 280	$ 283		Brazil	$ 225	$ 290
7	FX	$ 30	$ 55		LATAM	$ 225	$ 290		France	$ 190	$ 300
8	Lending	$ 25	$ 45
9
Sheet1

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1					LTM				PLTM
2	ID	Banker ID	Region	Country	Core cash	Liquidity	FX	Lending	Core cash	Liquidity	FX	Lending
3	12345	22	APAC	India	$ 55	$ 65	$ 75	$ 85	$ 95	$ 45	$ 65	$ 78
4	55647	44	EMEA	France	$ 29	$ 89	$ 55	$ 65	$ 75	$ 85	$ 95	$ 45
5	12345	66	LATAM	Brazil	$ 30	$ 55	$ 65	$ 75	$ 85	$ 95	$ 45	$ 65
6	12345	88	EMEA	France	$ 25	$ 45	$ 55	$ 65	$ 75	$ 85	$ 95	$ 45
Sheet2

Sheet 1 have 3 different scenario.


Scenario 1 (ID & LTM/PLTM
Scenario 2 (ID & Region & LTM/PLTM)
Scenario 3 (ID & Country & LTM/PLTM)


Sheet 2 have Raw data


Scenario 1 (ID & LTM/PLTM - if ID (Cell B2 i.e.12345), Product is Corecash (A5) and time period is LTM (B4) then
cell b5 should do sum of all these conditions and return value $ 80

Scenario 2 (ID & Region & LTM/PLTM) - if ID (Cell B2 i.e.12345), Region is EMEA (E5) and
time period is LTM (F4) then cell F5 should do sum of all these conditions and return value $ 190

Scenario 3 (ID & Country & LTM/PLTM) - if ID (Cell B2 i.e.12345), Country is India (I5) and
time period is LTM (J4) then cell J5 should do sum of all these conditions and return value $ 280


based on data in sheet 2 i should get the output numbers in sheet 1

Is it possible?

 

[nikhil0311]

Can anyone please help me on this.

 

[FlameRetired]

In Scenario 1 there are 3 instances of ID 12345.

The array is {55;0;30;25}. It's sum is 110.

What am I missing?

 

[nikhil0311]

My sincere apologies. You are right FlameRetired. please find the revised scenario output

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Scenario 1 (ID & LTM/PLTM				Scenario 2 (ID & Region & LTM/PLTM)				Scenario 3 (ID & Country & LTM/PLTM)
2	ID	12345
3
4	Product	LTM	PLTM		Region	LTM	PLTM		Country	LTM	PLTM
5	Core cash	$ 110	$ 255		EMEA	$ 190	$ 300		India	$ 280	$ 283
6	Liquidity	$ 165	$ 225		APAC	$ 280	$ 283		Brazil	$ 225	$ 290
7	FX	$ 195	$ 205		LATAM	$ 225	$ 290		France	$ 190	$ 300
8	Lending	$ 225	$ 188
Sheet1

 

[Aladin Akyurek]

In B5 control+shift+enter, not just enter, and copy down:

=SUM(IF(Sheet2!$A$3:$A$6=$B$2,INDEX(Sheet2!$E$3:$H$6,0,MATCH($A5,Sheet2!$E$2:$H$2,0))))

In C5 control+shift+enter, not just enter, and copy down:

=SUM(IF(Sheet2!$A$3:$A$6=$B$2,INDEX(Sheet2!$I$3:$L$6,0,MATCH($A5,Sheet2!$I$2:$L$2,0))))

The rest should follow the same logic...

 

[nikhil0311]

Thank You Aladin! your formulas works like a charm. i have 2 quick question.

1 - How do i make the range dynamic? the no. of records in sheet 2 will be dynamic.

2- what if i have to add 1 or 2 more conditions to sum?

 

[nikhil0311]

Also I am not able to run the formula for scenario 2, can you plz help?

 

[Aladin Akyurek]

Also I am not able to run the formula for scenario 2, can you plz help?

F5, control+shift+enter and copy down:

=SUM(IF(Sheet2!$A$3:$A$6=$B$2,IF(Sheet2!$C$3:$C$6=$E5,INDEX(Sheet2!$E$3:$H$6,0,MATCH($A5,Sheet2!$E$2:$H$2,0)))))

G5, control+shift+enter and copy down:

=SUM(IF(Sheet2!$A$3:$A$6=$B$2,IF(Sheet2!$C$3:$C$6=$E5,INDEX(Sheet2!$I$3:$L$6,0,MATCH($A5,Sheet2!$I$2:$L$2,0)))))

 

[nikhil0311]

Thank You Sir. Appreciate your help!!

 

[Aladin Akyurek]

Thank You Sir. Appreciate your help!!

You are welcome.