The ExcelHelpDesk Support team received the following request for help
Problem – Calculate Sum of Hours for Activity Code
I have three columns running down two pages. Columns A, B and C. A equals a start time, B equals ending time. Both are using military time. Column C represents an activity selected from a pick list. Such as “Patrol.” How do I write a function or formula to automatically give me the total amount of time spent on “Patrol” for the whole workbook for the activity of “Patrol” only? For example
Col A Col B Col C
08:00 10:00 Patrol
10:00 11:00 Lunch
11:00 12:00 Patrol
I need a cell to illustrate Patrol=3 hours
For this problem we received a copy of the workbook. We then used a formula to calculate the number of hours between A and B for the activity. Lastly we created a formula to SUM all the activities for the daily log.
SUMIF formula for Activity Codes
See below an example of the activity log data provided
Step 1 – Create formula to calculate the Hours
The first step is to create the formula that will calculate the number of hours for the activity in the log. In this case we have the “Initiated Time” in column A, “Clear Time” in column B. The “Total Hours” will be included in column D, starting with cell D16 the formula will be
Make sure D16 is “Time” format before including the formula. This will ensure that the calculation shows the hours in a time format. Once the formula is created in D16 then it is dragged down for the remaining rows. See the formula below.
Step 2 – SUMIF to Total the values by Activity code
Now using a list of all available “Activity” codes a SUMIF formula is created to calculate the total number of hours in the Activity Log for each Activity. The formula will use a RANGE of cells to determine the data that is to be included to the calculation. The RANGE needs to include the Activity code as the first column in the range. This is important when we select the criteria as the first column is used to match for the SUM of the values. The range in this case will include the Activity Code and the Total Hours for that Activity in the log. See below for the RANGE selection in this case.
Now the SUMIF formula is created using the RANGE above as the data source. See below the RANGE is specified as $C$16:$D$23 which includes the Activity Code and the Total Hours. The CRITERIA is specified as the Activity Code in the Daily Totals table in this case A96. Lastly we need to specify the SUM_RANGE in this case the numbers we want to total are in the column next to the Activity Code. So the SUM_RANGE is $D$16:$D$23.
Finally the SUMIF formula is dragged down the rest of the Activity Codes to calculate the total hours in the Activity Log for each of the codes. See the image below for the final view with the SUMIF formula calculating hours for the Activities that are listed in the Activity Log.
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment