If statement with Match & Index

[jlugo]

I'm trying to create a formula to scan for certain GL codes in the row headers.
If the intersected codes match, then add each value up.
(i.e. Column A headers GL codes 6310 + 6500 + 6510 + 6540, their values to the right - Column B $345, $456, $567, & $678).


Here is a sample of data:

[TABLE="class: cms_table, width: 690"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Exchange Rate Income/Loss-INR[/TD]
[TD]6001[/TD]
[TD="align: right"]2,214[/TD]
[TD="align: right"]1,757[/TD]
[TD="align: right"](431)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Exchange Rate Income/Loss-GBP[/TD]
[TD]6002[/TD]
[TD="align: right"]1,203[/TD]
[TD][/TD]
[TD="align: right"]1,568[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gain/Loss on Sale/Writeoff of Assets[/TD]
[TD]6003[/TD]
[TD][/TD]
[TD="align: right"]778[/TD]
[TD="align: right"]88[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other Non-Operating Income[/TD]
[TD]6005[/TD]
[TD="align: right"](6,514)[/TD]
[TD="align: right"](8,286)[/TD]
[TD="align: right"](13,469)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee Deferred Compensation Cost[/TD]
[TD]6310[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]1,769[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Discontinued Operation Expenses[/TD]
[TD]6500[/TD]
[TD="align: right"]5,343[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Facility Relocation Expenses[/TD]
[TD]6510[/TD]
[TD="align: right"]41,299[/TD]
[TD="align: right"]60,894[/TD]
[TD="align: right"]5,106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Transaction Related Expenses[/TD]
[TD]6520[/TD]
[TD="align: right"]7,344[/TD]
[TD="align: right"]5,058[/TD]
[TD="align: right"]1,060[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Non-Recurring Legal Expenses[/TD]
[TD]6530[/TD]
[TD="align: right"]9,536[/TD]
[TD="align: right"]1,938[/TD]
[TD="align: right"]235[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Non-Recurring Personnel Expenses[/TD]
[TD]6540[/TD]
[TD="align: right"]57,117[/TD]
[TD="align: right"]38,871[/TD]
[TD="align: right"]3,117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]System Conversion Consulting Expenses[/TD]
[TD]6550[/TD]
[TD="align: right"]12,410[/TD]
[TD="align: right"]7,410[/TD]
[TD="align: right"]10,755[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other Non-Recurring Expenses[/TD]
[TD]6590[/TD]
[TD="align: right"]78,295[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Suspense[/TD]
[TD]9899[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,203[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other non-recurring (income)/expenses[/TD]
[TD][/TD]
[TD="align: right"]75,198[/TD]
[TD="align: right"](5,751)[/TD]
[TD="align: right"](12,244)[/TD]
[TD]Desired Results[/TD]
[/TR]
[TR]
[TD="colspan: 5"]Calculation - Other non-recurring (income)/expenses - (6001 + 6002 + 6003 + 6005 + 6590)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am trying to create an if statement that would scan the column headers and scan the row headers that match the 6xxx account criteria. If it matches, it will perform either addition or subtraction type calculations.

Cell reference is not an option because rows are not static and always being switched around.

Thanks!

 

[J.Ty.]

Is this something you are looking for?

Book1
	A	B	C	D	E	F	G	H
1	is a sample of data:
2
3			JAN	FEB	MAR	APR
4	Exchange Rate Income/Loss-INR	6001	2,214	1,757	-431
5	Exchange Rate Income/Loss-GBP	6002	1,203		1,568
6	Gain/Loss on Sale/Writeoff of Assets	6003		778	88
7	Other Non-Operating Income	6005	-6,514	-8,286	-13,469
8	Employee Deferred Compensation Cost	6310	258	260	1,769
9	Discontinued Operation Expenses	6500	5,343
10	Facility Relocation Expenses	6510	41,299	60,894	5,106
11	Transaction Related Expenses	6520	7,344	5,058	1,06
12	Non-Recurring Legal Expenses	6530	9,536	1,938	235
13	Non-Recurring Personnel Expenses	6540	57,117	38,871	3,117
14	System Conversion Consulting Expenses	6550	12,41	7,41	10,755
15	Other Non-Recurring Expenses	6590	78,295
16	Suspense	9899			1,203
17
18
19	Other non-recurring (income)/expenses		239,076	244,304	-22,455
20
21
22
23	Specify codes to add/subtract	plus code1	plus code2	plus code3	plus code4	minus code1	minus code2	minus code3
24		6005	6310			6550
Sheet7

Cell Formulas
Range	Formula
C19	{=SUM(SUMIF($B$4:$B$16,$B$24:$E$24,C4:C16))-SUM(SUMIF($B$4:$B$16,$F$24:$H$24,C4:C16))}
D19	{=SUM(SUMIF($B$4:$B$16,$B$24:$E$24,D4:D16))-SUM(SUMIF($B$4:$B$16,$F$24:$H$24,D4:D16))}
E19	{=SUM(SUMIF($B$4:$B$16,$B$24:$E$24,E4:E16))-SUM(SUMIF($B$4:$B$16,$F$24:$H$24,E4:E16))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Note that cells B24 to H24 have data validation, which provides you with a drop-down list with the codes to choose from.

J.Ty.

 

[alfranco17]

(posted deleted by alfranco17)

 

[jlugo]

Thank you for replying!

Sum If is under the condition data is static so a cell reference won't work. It is likely these rows could move.

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

[TABLE="width: 554"]
<colgroup><col><col><col span="2"><col></colgroup><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]

2,214, 2,960, (343) = Desired Results

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!

 

[J.Ty.]

Thank you for replying!

Sum If is under the condition data is static so a cell reference won't work. It is likely these rows could move.

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


[TABLE="class: cms_table_cms_table, width: 690"]
<tbody>[TR]
[/TR]
[TR]
[TD][/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Exchange Rate Income/Loss-INR[/TD]
[TD]6001[/TD]
[TD="align: right"]2,214[/TD]
[TD="align: right"]1,757[/TD]
[TD="align: right"](431)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Exchange Rate Income/Loss-GBP[/TD]
[TD]6002[/TD]
[TD="align: right"]1,203[/TD]
[TD][/TD]
[TD="align: right"]1,568[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gain/Loss on Sale/Writeoff of Assets[/TD]
[TD]6003[/TD]
[TD][/TD]
[TD="align: right"]778[/TD]
[TD="align: right"]88[/TD]
[/TR]
</tbody>[/TABLE]

2,214 2,960 (343) = Desired Results

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.

I do not understand. You say we want to add values from rows with headers 6001 and 6003 and then you produce, as the desired result, a sum of three values, two of which do not occur in the table.

J.Ty.

 

[jlugo]

Hi J.Ty.,

I see the confusion. I re-edited my grid because it did not past correctly. I see you responded to the incorrect grid. January is over the account #s (which wasn't supposed to happen. The only accounts adding up are 6001 & 6003. The 6002 is skipped in the desired outcome totals. Trying to figure a way to build certain accounts into the criteria no matter where they are in the column headers or row header locations.

 

[jlugo]

paste correctly... Typo

 

[J.Ty.]

I still do not see 2,960 and (343) in the table, so I still do not know what you want to calculate.
I have made a simple spreadsheet. Download it, test and tell me how it relates to your needs.
It is here: http://mimuw.edu.pl/~jty/MrExcel/jlugo.xlsx

J.Ty.

 

[jlugo]

Thanks for the attachment!
I added the sums based on your template to illustrate the desired results. For each month it's adding down accounts 6001 + 6003 (Skipping 6002).


[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]January[/TD]
[TD="width: 64"]February[/TD]
[TD="width: 64"]March[/TD]
[/TR]
[TR]
[TD]INR[/TD]
[TD="align: right"]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="align: right"]6002[/TD]
[TD="align: right"]1.203[/TD]
[TD][/TD]
[TD="align: right"]1.568[/TD]
[/TR]
[TR]
[TD="class: xl64"]Gain/Loss[/TD]
[TD="class: xl64, align: right"]6003[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]778[/TD]
[TD="class: xl64, align: right"]88[/TD]
[/TR]
[TR]
[TD="class: xl63"]Sum[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]2.214[/TD]
[TD="class: xl63, align: right"]779.757[/TD]
[TD="class: xl63, align: right"]-343[/TD]
[/TR]
</tbody>[/TABLE]

The row headers are subject to move up or down a row if they choose to delete an account (i.e. 6002 row gets deleted then 6003 moves up).

Let me know if this makes sense.

 

[J.Ty.]

Download it again and have a look at the second worksheet.

J.Ty.