SUMIFS Dynamic range

[LBTravis]

I need to sum data for each department. This seems simple but my data's columns are not always in the same order. I'm sure there is a way to find the 'department column in an array then find the wages column and sum particular department names.

It just needs to work with dynamic data.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Department[/TD]
[TD]Wages[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD]Admin[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spalding, Sally[/TD]
[TD]Sales[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Clooney, George[/TD]
[TD]Admin[/TD]
[TD]700[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sales[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]Revere, Paul[/TD]
[TD]SAles[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD]Admin[/TD]
[TD]1700[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Try:

=SUMIFS(INDEX(A:C,0,MATCH("wages",INDEX(A:C,1,0),0)),INDEX(A:C,0,MATCH("department",INDEX(A:C,1,0),0)),"sales")

 

[LBTravis]

Thank you soooo much!!

Now what if I want to total the "Wages" column but I don't know what column tha's going to end up in.

Try:

=SUMIFS(INDEX(A:C,0,MATCH("wages",INDEX(A:C,1,0),0)),INDEX(A:C,0,MATCH("department",INDEX(A:C,1,0),0)),"sales")[

 

[Aladin Akyurek]

Thank you soooo much!!

Now what if I want to total the "Wages" column but I don't know what column tha's going to end up in.

The formula I proposed determines itself the wages column (the column range to sum) and the department column (the condition range) automatically.

 

[LBTravis]

So if I remove the "department" condition it will total the entire wages column?

 

[Aladin Akyurek]

So if I remove the "department" condition it will total the entire wages column?

Where are you heading? SUMIF (also SUMIFS) requires at least 1 condition.