Formulas to pull hours and dollars based on dept type

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
226
Office Version
  1. 2013
  2. 2007
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:

ABC
1Dept TypeHOURSDollars
2Warehouse Worker Regular
3Warehouse Worker Overtime
4MaintenanceN/A
5TransportationN/A
6MechanicsN/A
7Warehouse ClerksN/A
8Inventory ClerksN/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?
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
226
Office Version
  1. 2013
  2. 2007
Shoot, I realized I didn't clarify a detail that I think will change this, not sure? So for the departments that require a dollar amount only, that dollar amount is actually to be for regular and OT hours ONLY. There may be a possibility that they have Sick Time Vacation time, etc. Those dollars are not to be included. Sorry I just thought of that as I realized my totals for the maintenance department encompassed more than just Regular and OT hours.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,586
Office Version
  1. 365
  2. 2010
Since my formula checks for REG and OT, a cell with SICK, etc. won't be included.
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
226
Office Version
  1. 2013
  2. 2007
Does it also check for REG and OT in the maintenance group? I just hadn't seen anything with REG and OT in the maintenance groups formula and just wondered how it might know?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,586
Office Version
  1. 365
  2. 2010
Yes. Look at the formulas again where I checked for "reg" or "ot" (adjust to your own types of entries: e.g., "Regular" or "Overtime", etc.)
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
226
Office Version
  1. 2013
  2. 2007

ADVERTISEMENT

Somethings wrong. To clarify any, I am talking about the formula that uses "Maintenance Building", "Parts Clerk". I use that formula and I am getting a total, but the total is for REG, OT, and every other type of hour classification. I had inserted a duplicate row for a parts clerk for example and just put SICK as the type and just for the heck of it put 1,000 hours. It updated my overall total by that 1,000.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,586
Office Version
  1. 365
  2. 2010
I thought Maint was ONLY calculating $, not hours.

If you need to alter the $ column C, try:

Code:
=SUMPRODUCT((Sheet2!D2:D100)*(Sheet2!B2:B100<>"Sick")*(("Battery Person"=Sheet2!E2:E100)+("Maint Bldg"=Sheet2!E2:E100)+("Refrig"=Sheet2!E2:E100)+("Parts Clerk"=Sheet2!E2:E100)))
 
Last edited:

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
226
Office Version
  1. 2013
  2. 2007

ADVERTISEMENT

Maint is only calculating dollars but only the dollars within the REG and OT hours types. The warehouse was different because they want to see the amount of REG hours associated with the REG dollar figure, same with OT. All other departments they just wanted to see the dollar figure associated with REG and OT, not the hour amount.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,586
Office Version
  1. 365
  2. 2010
You should be able to work with what I've shown you and adjust the other calculations accordingly.
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
226
Office Version
  1. 2013
  2. 2007
Could it do it based on if it equals REG or OT? instead of <> SICK? I just ask because there are actually a TON of different pay codes besides Sick, I was only using that one as an example. would it be something like how you have at the beginning:
(Sheet2!B2:B100<>"Sick")
but instead
(Sheet2!B2:B100="REG","OT")

I don't know what the syntax would be to include multiple paycodes like that.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,586
Office Version
  1. 365
  2. 2010
Break them separately and use "+" for an "or" condition.
Like: (sheet2!B2:B100="REG")+(sheet2!B2:B100="OT")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top