Formulas for calculating total time

Hey everyone,

I am newer to Smartsheet and I am making a form from previous data to continue time tracking. I am trying to get the sheet to add all the hours together from one day to make a total time. It would also be specific to each individual.

Each person should be working 8 hours so I should be able to get a running total of how many hours a person works per day.

It would be nice if I could get the running total in the form but I think getting the formula right in the sheet would be best right now.

Im mostly stuck on how to get the criterion entered correctly to pull the summary because I can have it calculate for the whole day but not separated by individual

Find more posts tagged with

Accepted answers

All comments

KPH

Welcome @graromero9123

It sounds like you might be using a SUMIF function to add up the hours each day. You can use a SUMIFS to add additional criterion, which would allow you to also restrict the total to a particular individual. See:

https://help.smartsheet.com/function/sumifs

graromero9123

I have been trying it a few different ways but I dont know where i am messing up the formula

KPH

The syntax for SUMIFS is

=SUMIFS(range, criterion_range1, criterion1, [criterion_range2, criterion2, ...])

So if you want to sum the Study Hours, for a given name, before a given start date you need something like:

=SUMIFS([Study Hours]:[Study Hours], Name:Name, "Pete", [Start Date]:[Start Date], >=DATE(2025, 7, 1))

This means sum the study hours where Name is "Pete" and Start Date is after or on July 1st.

In this example:

The problems are:

Two columns are being summed
There is no name specified
A reference to one cell [Total Hours]1 is not needed in there
Instead of the date there is a partial reference [Start Date]. You could make this a reference like [Start Date]@row or [Start Date]1. This would work: =SUMIFS([Study Hours]:[Study Hours], Name:Name, "Pete", [Start Date]:[Start Date], [Start Date]1)

In this example:

The problems are:

A SUMIF can only have 1 criteria and you have many
name=name is not a valid criteria, it needs to be a value or a reference to a cell with a value in
There seem to be lots of ranges but no criterion.

In this example:

The syntax looks OK but the formula is not color coded which suggests a problem with the column refences.

In this example:

The problems are:

You are summing the name column which probably doesn't have numbers in it - the range to sum goes first in a SUMIFS
The next part means you will only sum rows where the value in the start date column is =. This should be a date.
Then you have lots of ranges and no criteria for them.

I hope that makes sense.

I suggest you start with one range and criteria, check it works and then add more, remembering to add pairs of ranges and criteria.

From

=SUMIFS([Study Hours]:[Study Hours], Name:Name, "Pete", [Start Date]:[Start Date], >=DATE(2025, 7, 1))

You can add another range and criteria pair to check the start date is before a date

=SUMIFS([Study Hours]:[Study Hours], Name:Name, "Pete", [Start Date]:[Start Date], >=DATE(2025, 7, 1), [Start Date]:[Start Date], <DATE(2025, 8, 1))

graromero9123

do you see where I am having issues in my formula agian? the last answers were helpful but I am still confused on adding ranges and criteria, my totals aren't summing and I would like to be able to apply it to the column so I am not sure if this is the correct way to get the names from each individual without having to change it everytime

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.