What formulas to use in a timesheet solution to SUM up values
Hello, I have used one of the great solutions that has been posted by, I believe, @Paul Newcome relating to tracking and working out time/duration of hours in a HH:MM format. There are 2 columns to achieve this - the first uses a formula to work out the numerical duration: =((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)
The second column then converts the number in the first column into HH:MM format using this formula:
=INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60
It is currently 1 mastersheet timesheet, with the intention of either using filters or individual reports for each staff member and a webform to add in the hours of what they've been working on. The team is an editorial team, who need to track the length of time that they've been editing a particular piece of content.
I've added in an additional couple of columns to suit the workflow of the team. What they also need is to summarise the totals on a monthly basis for each editor: overall hours total, then broken down into "editing" overall hours total and "marketing" overall hours total.
Editing tasks are defined by 4 values in the Delivery column: Ad Sales, Distribution, Editorial and News Channel.
Marketing tasks are defined by these 3 values: GN Marketing, GN Marketing Affiliate and Yahoo.
The first bit I'm struggling with is how to calculate the totals for Editing tasks and Marketing tasks. I know how to achieve this with 1 value: =SUMIF(Delivery:Delivery, "Editorial", SUM:SUM), for example, but how do I get the formula to add up multiple values too?
The second bit I'm struggling with is how to achieve SUM totals - I used a totals/summary row (indicated in grey) and was able to do this through the SUM function button but because I need a breakdown into 2 different types of tasks, as mentioned above, I think it would be better to do this in the sheet summary.
I've done a screenshot of the sheet for reference below:
Then here's a screenshot of the sheet summary:
All advice and guidance would be much appreciated, as formulas are not my area of expertise!
Help Article Resources
Check out the Formula Handbook template!