SUMIFS using 2 variables in changing columns

[vespagtguy]

Greetings,

I have a table that is generated by my payroll company. I need to generate some data based upon this table:

Employee	Salary	Location	Dept
aaa	3500	50	100
bb	4000	501	100
ccc	3000	50	100
ddd	4000	50	200
eee	5000	50	200
fff	3000	502	100
ggg	7000	50	100
hhh	10000	50	100

<tbody>
</tbody>

I want to find the sum of the salaries (column B) for employees in location 50 (Column C), dept 100 (ColumnD).
This is straight forward as I created the following SUMIFS calculation:

=SUMIFS($B$2:$B$9,$C$2:$C$9,"50",$D$2:$D$9,"100") - Works Fantastic!

Now for my dilemma...
the payroll company has moved my columns either by adding or removing columns which changes the cells that I am performing my lookups upon. The values I want to add are no longer in cloumn B, but are now in column C; and so forth. Such as:

Employee	Check #	Salary	Location	Dept
aaa	10001	3500	50	100
bb	10002	4000	501	100
ccc	10003	3000	50	100
ddd	10004	4000	50	200
eee	10005	5000	50	200
fff	10006	3000	502	100
ggg	10007	7000	50	100
hhh	10008	10000	50	100

<tbody>
</tbody>

Is there a way that I can perform this type of lookup based upon the column headers, as the text will not change, but the column location may? For example:
=SUMIFS([Salary],[Location],"50",[Dept],"100")

I know it is not as simple as the above. I just cant seem to grasp the multiple column lookup. Not sure if there would need to be a combination of INDEX and MATCH. Perhaps I'm over-thinking? Thanks in advance.

-vespagtguy

 

[Jonmo1]

=SUMIFS([Salary],[Location],"50",[Dept],"100")

I know it is not as simple as the above.

Actually, if you convert the range to a table, then it really IS just that simple.

Highlight the whole range, Click Insert - Table
Check "My table has headers"
Finish.

Now you can use something like
=SUMIFS(TableName[Salary],TableName[Location],"50",TableName[Dept],"100")

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E	F	G	H
1	Employee	Salary	Location	Dept		Salary	Location	Dept
2	aaa	3500	50	100		23500	50	100
3	bb	4000	501	100
4	ccc	3000	50	100
5	ddd	4000	50	200
6	eee	5000	50	200
7	fff	3000	502	100
8	ggg	7000	50	100
9	hhh	10000	50	100
Sheet9

Cell Formulas
Range	Formula
F2	=SUMIFS(B2:B9,INDEX(A2:D9,0,MATCH(G1,A1:D1,0)),G2,INDEX(A2:D9,0,MATCH(H1,A1:D1,0)),H2)

 

[vespagtguy]

it really IS just that simple.

Jonmo1,

You, my friend, are a GENIUS! I consider myself fairly knowledgeable in Excel, but I never even thought of converting the data to a table. I spent a few hours yesterday going down the wrong path by combining different functions that just did not work. Thank you so much for your prompt and spot-on response! The next time you're in Ft. Lauderdale, I'll buy you a beer...or two...or three. Thanks again.

-vespagtguy

 

[vespagtguy]

@AlanY - I'll give your solution a test too. Thanks!

 

[Jonmo1]

You're welcome.

Many advantages to using tables.
You no longer have to worry about your formulas referring to ranges that expand over time, it's taken care of automatically.
When you add a new record to the table, formulas are automatically filled down.

 

[vespagtguy]

The spreadsheet contains much more data than just the three columns I was asking for assistance with. Your solution will work for the entire spreadsheet. Our company has over 300 Employees in 5 states. The employees change, the taxes and rates vary, the IRA / 401K change. This is an all encompassing solution. Many thanks.