If statement

[jlugo]

Hi,


Question 1.
I'm trying to build a data drop sheet. My goal is to create an if statement that reads the column headers and row headers. If both read a certain word "lets say a month & employee wage" then to return the value where they both intersect.

Question 2.
Same concept as above, but I'm trying to create a formula to scan for certain GL codes in the row headers. If these code are identified, 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).

Any help is appreciated.

 

[jamtay317]

this is the anwser the first question, and the second I think that I need some sample data to understand what you are talking about, can you please fill all borders around a small sample of the data, highlight it then copy and past it in here

Excel 2010
	A	B	C	D	E
1	Month	Employee Wage	Address	City	State
2	January	$10.00	123 main Street	that City	that State
3	February	$11.00	124 main street	that City	that State
4	March	$12.00	125 main Street	that City	that State
5	April	$13.00	126 main Street	that City	that State
6	May	$14.00	127 main Street	that City	that State
7	June	$15.00	128 main Street	that City	that State
8
9
10
11		February	Employee Wage	$11.00
Sheet1

Cell Formulas
Range	Formula
D11	=INDEX(B2:E7,MATCH(B11,A2:A7,0),MATCH(C11,B1:E1,0))

 

[jlugo]

Here is a sample of data:

[TABLE="width: 690"]
<colgroup><col><col><col><col><col><col></colgroup><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.