What formulas to use in a timesheet solution to SUM up values

SteCoxy ✭✭✭✭✭✭

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!

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!