Totaling OT Hours on Separate Sheet [Closed]

Report
Posts
7
Registration date
Wednesday February 15, 2017
Status
Member
Last seen
February 28, 2017
-
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
-
Good Morning!

https://drive.google.com/file/d/0Byqv0sZOGZaHRUNuUlNUc1RKbWM/view?usp=sharing

I have a spreadsheet that managers fill out with overtime hours. At the top you will see some drop down boxes that are populated by information on the second sheet. I am looking to have a "totals" tab that will total by code, and by either "OT" or "CE" for each employee. These are entered by instance so there are often weeks that certain codes are used multiple times and I would like to have a total sheet of which I could easily enter information into my payroll system.

Any help is greatly appreciated!

Thank you in advance!

Andrea

2 replies

Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
446
Hi Andrea,

Create a total sheet and let us know with which data pulls you are having trouble with.

Best regards,
Trowa
Posts
7
Registration date
Wednesday February 15, 2017
Status
Member
Last seen
February 28, 2017

Trowa - I'm having trouble creating a formula which reads the codes entered in "OT Sheet" and based on the codes totals the hours in "Sheet 2" Is this possible or do I need to list out all the possible codes and total them that way?

Thanks for your help!

Andrea
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
446
Hi Andrea,

So when you enter code 01501 in C8 of the OT sheet, you would like to know the sum of C11:D30. When you enter code 01501 in K8 as well, you would like to know the sum of C11:D30 + K11:L30.

If the above is what you are after, then I would place a sum formula in row 31.
Cell C31: =SUM(C11:D30)
Cell E31: =SUM(E11:F30)
and so on.

Now you can use the following formula:
=COUNTIF($C$8:$Z$8,"01501",$C$31:$Z$31)
You can replace the "01501" with a cell reference where that value is located.
On sheet "Sheet1" you have listed the codes in column A. When you would like to put the total in column E, your formula would be:
=COUNTIF('OT Sheet'!$C$8:$Z$8,A1,'OT Sheet'!$C$31:$Z$31)
Then drag this formula down to get the result for the other codes.

Note that "Sheet2" is empty in your uploaded file.

Best regards,
Trowa