Index Match & nested sum function

[jlugo]

Hi,

I'm trying to return the values of the intersection of column & row headers. The rows are not static, so cell reference would not be a good long term solution.

To make it simple, let's say we want to add 6001 + 6003 only:

[TABLE="class: cms_table, width: 554"]
<tbody>[TR]
[TD][/TD]
[TD]Acct[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INR[/TD]
[TD]6001[/TD]
[TD="align: right"]2,214[/TD]
[TD="align: right"]1,757[/TD]
[TD="align: right"](431)[/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD]6002[/TD]
[TD="align: right"]1,203[/TD]
[TD][/TD]
[TD="align: right"]1,568[/TD]
[/TR]
[TR]
[TD]Gain/Loss[/TD]
[TD]6003[/TD]
[TD][/TD]
[TD="align: right"]778[/TD]
[TD="align: right"]88[/TD]
[/TR]
</tbody>[/TABLE]


Desired results:
6001 + 6003 = desired results (6002 is skipped)

January = (2,214 + 0) = 2,214
February = (1,757 + 778) = 2,535
March = (-431 +88) = -423

What function(s) scans the column headers & row headers (in case they move) to add up those amounts if they intersect? The goal is the perform this calculation for each month.

Thanks for your help!

 

[Akashwani]

Hi jlugo.

Does this work for you?...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		Acct	January	February	March		Criteria1	Criteria2	Result
2	INR	6001	2,214	1,757	-431		GBP	Acct	6002
3	GBP	6002	1,203	1,568					6002
4	Gain/Loss	6003	778	88
5
Sheet3

If this is not the solution you require, then take a look here....

excelisfun -- Excel How To Videos - YouTube

Mike has plenty of excellent videos that may be of help.

Good luck.

Ak

 

[jlugo]

HI Akashwani,

Actually, I'm a big fan of youtube video and I already am a subscriber to Exceisfun. He even made me a video once

You are close!

Note - 6002 is being skipped altogether. Not part of the criteria.

Criteria 1= January, Criteria 2 = INR, Criteria 3 = GBP, So then add criteria 2 + 3 = desired result

Thanks for your help.