Formulas to pull hours and dollars based on dept type

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
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
224
Office Version
  1. 2013
  2. 2007
Awesome, this works, I checked it and it is totaling only what I want it to, so thank you very much for all the help and troubleshooting! Something I am curious about though, why in this formula where you have (sheet2!B2:B100="REG") for example, Why can you not have (sheet2!B:B="REG") to basically just include the entire column? I did have to expand that 100 and just made it 9000 as there are several thousand rows worth of data each week but when I tried using (sheet2!B:B="REG"), it just gives an error message. Its not until I have an actual range in there like 2:9000 that it actually works. Do you know why this is so?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,565
Office Version
  1. 365
  2. 2010
Yes, you are correct. You can use the full column like you indicated. But, you have to be sure you use the full columns for EVERY comparison in the formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,785
Members
415,930
Latest member
davidentekrity

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