Formulas to pull hours and dollars based on dept type

[bh24524]

Hi, I have a spreadsheet that has 2 tabs. The first tab is just an overall dept. Summary tab to ideally give hour totals and dollar totals based on information that is pasted weekly in the second tab. The headers in the first tab look like this:

	A	B	C
1	Dept Type	HOURS	Dollars
2	Warehouse Worker Regular
3	Warehouse Worker Overtime
4	Maintenance	N/A
5	Transportation	N/A
6	Mechanics	N/A
7	Warehouse Clerks	N/A
8	Inventory Clerks	N/A

They are only looking for hours for the Warehouse workers but they do want dollars for them and everyone else.


Tab two contains several different headers, but the ones relevant to here would be Earn Code (REG, Overtime, etc.), Hours Amount, Dollar Amount, and Position description
Here is where it gets a little tricky. Maintenance encompasses several positions, Battery Persons, Refrigeration Technicians, General Maintenance, and Parts Clerks, but they are all to be reported under one total - maintenance.

My question is can there be some kind of formula on tab 1 that will encompass the hour totals and dollar totals, and lump together what positions are within one department. What would be the best way to do this?

 

[kweaver]

Could you display a sample of the 2nd sheet And, if it's not going to be obvious to people in here, show some answers you expect on the first sheet.

 

[bh24524]

Okay, so here is the sample of tab 2:

So if we look back at the 1st Tab where it has the summary, in this case, I would want the following:

*For the Maintenance category, I want to have just a dollar total for Battery Person, Maintenance Building, Refrigeration Technicians, and parts clerks. (I didn't use the exact names of those positions to the T but you get the idea) So those 4 all fall under the Maintenance category on tab 1 and I just want a combined dollar total in there.
*For the category Warehouse Worker in tab 1, that is the one where I am wanting a sum of all REG hours and the dollar amount associated with it from tab 2 - anyone who has Warehouse Worker-RP as their description. I also want the same for Overtime hours and dollars.

The maintenance and Transportation categories are the only ones that have multiple position types (Descrp column) - Maintenance has the four I mentioned and Transportation has two.

I hope this helped to clarify anything.

 

[kweaver]

If you would use XL2BB no one has to try to replicate your data. Thanks.

Also, are the hours in C and the dollars in D?

 

[bh24524]

Yes hours are in C and dollars in D. They have restrictions on this computer as far as downloading files so unfortunately, I can't use the XL2BB else I would.

 

[kweaver]

It appears you used it in your original post, however.

 

[bh24524]

I actually hand-typed those. I inserted blank rows and columns and bolded, numbered, and labeled them as they would normally appear in Excel. I don't know if that looks identical to how XL2BB would do it. The second I just snipped and pasted

 

[kweaver]

Does this get you started?

Book2
	A	B	C
1	Dept Type	HOURS	Dollars
2	Warehouse Worker Regular	6.33	159.33
3	Warehouse Worker Overtime	3.46	131.80
4	Maintenance		2282.36
5	Transportation
6	Mechanics
7	Warehouse Clerks
8	Inventory Clerks
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT((Sheet2!$C$2:$C$100)*("Regular"=LEFT(Sheet2!$B$2:$B$100,7))*("Warehouse"=LEFT(Sheet2!$E$2:$E$100,9)))
B3	B3	=SUMPRODUCT((Sheet2!$C$2:$C$100)*("OT"=LEFT(Sheet2!$B$2:$B$100,2))*("Warehouse"=LEFT(Sheet2!$E$2:$E$100,9)))
C2	C2	=SUMPRODUCT((Sheet2!$D$2:$D$100)*("Regular"=LEFT(Sheet2!$B$2:$B$100,7))*("Warehouse"=LEFT(Sheet2!$E$2:$E$100,9)))
C3	C3	=SUMPRODUCT((Sheet2!$D$2:$D$100)*("OT"=LEFT(Sheet2!$B$2:$B$100,2))*("Warehouse"=LEFT(Sheet2!$E$2:$E$100,9)))
C4	C4	=SUMPRODUCT((Sheet2!D2:D100)*(("Battery Person"=Sheet2!E2:E100)+("Maint Bldg"=Sheet2!E2:E100)+("Refrig"=Sheet2!E2:E100)+("Parts Clerk"=Sheet2!E2:E100)))

 

[bh24524]

Yes, I do believe that will work. I am curious though if this is a situation that could alternatively utilize the SUMIFS function? I've never used Sumifs before but just thought of it now and wondered.

 

[kweaver]

I'm actually not sure about SUMIFS because you have "or" conditions in at least one of the relationship and SUMIFS is a series of "and" conditions. Maybe it would work in for the others.