# Totaling OT Hours on Separate Sheet

Closed
nowlanandrea Posts 6 Registration date Wednesday February 15, 2017 Status Member Last seen February 28, 2017 - Updated by nowlanandrea on 28/02/17 at 08:48 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 7, 2017 at 11:04 AM
Good Morning!

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!

Andrea

## 2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 28, 2017 at 11:56 AM
Hi Andrea,

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

Best regards,
Trowa
nowlanandrea Posts 6 Registration date Wednesday February 15, 2017 Status Member Last seen February 28, 2017
Feb 28, 2017 at 12:03 PM
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?

Andrea
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 7, 2017 at 11:04 AM
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.