How to sum multiple cells across a sheet based on the value of each individual cell?

[jbluemke]

I have a sheet that I am using to keep track of employee hours . SOMETIMES they are used in OTHER DEPARTMENTS - which I need to keep track of/change accordingly

I currently have their normal Depts and hours figured out. BUT am unsure of the correct formula to add up hours that were performed in OTHER DEPARTMENTS than their usual.
For example:



Dept Shift Name Monday May 1 Tuesday May 2 ...............etc
Normal Dept Hrs OTHER DEPT Hrs Normal Dept Hrs OTHER DEPT Hrs
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Panels 1st Dan Smith 4 4 GATE DEPT 6 2 GATE DEPT



So, as above, Dan worked for 4 hours in his normal (Panels) Dept on Monday and then 4 more hours in the GATE DEPT. On TUESDAY he work 6 in Panels and 2 in GATES

And imagine a list of 200 employees with similar entries for the whole month (which i am segregating into WEEKS 1-4 or 5) ....

I am trying to have total Hours for every DEPT

So based solely on the above.... my report for the Panel Dept would be 10 hours ( 4 monday, 6 tues ) ..... and the Gate Dept would be 6 (4 monday, 2 tuesday)

The "NORMAL Hours" reference the dept next to their name.......... while OTHER DEPT has a drop down menu listing all the Depts and they choose which dept- if they worked elsewhere or not.

So, Im trying to figure out how to scan the whole sheet (or more accurately , the whole range of week 1 , week 2 etc) to see if anyone worked in OTHER DEPTS.... and sum those total hours into their respective depts.


Am I making any sense? lol

 

[jbluemke]

For NOW , I am starting to do a simple SUMIF at the bottom of the sheet, listing each dept and totalling the OTHER dept (to add to the time total).

example: Gates: day 1 (sumif J2:J200="Gates") .... then it adds that to the NORMAL GATE TTL HOURS. I'd just hate to do that for each day of the month and each department!

Hopefully there is an easier way

 

[jbluemke]

Nevermind. I think I am just going to SUMIF each day for each DEPT and then total everything up the long way.

Thank you anyway!