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!

 

[jlugo]

I don't see the attachment?

 

[J.Ty.]

Same url as before, I have uploaded a new version there.

J.Ty.

 

[jlugo]

Hi,

Ok. Just checked the revised sheet.

6002 figures are being skipped (1.203 & 1.568).
That is part of the parameters to read column headers & row headers and add where they intersect.
I included 6002 as an example of an account that needs to be omitted no matter where they are positioned.

6001 = (2.214 + 0) = 2.214
6002 = Skipped, not part of calculation because doesn't meet criteria
6003 = (-431 + 88) = -423

How do I add the intersection of matching rows/columns based on my criteria (6001 & 6003 only)?

Hope this clarifies.

 

[J.Ty.]

Look at row 10. You can choose there which accounts you want to add and which to subtract.

So if you want to add 6001 and 6003, choose 6001 and 6003 in row 10 under "Add" in columns C and D, remove the account under "Subtract" in row E and the formulas will calculate what you need.

J.Ty.

P.S. This is my last post for today, I'm going to bed. Good night.

 

[jlugo]

Thanks for your help!