Sum of values excluding rows with auto sum function

Brandi B.

Hi All,

I am fairly new to using Smartsheet, so I hope I am not asking a silly question. Is there a way to exclude certain lines that have the auto sum function? For example, I want to get a YTD sum of Hours Completed, but when I use the function, it shows that the YTD is 12 hours, but it should be 6.

Currently, the sum of Weeks 1 - 5 is totaled up at the top to get 6 hours. But when I do the YTD total, I am getting 12 hours. These are the only hours inputted so far. I hope that makes sense.

Jason Tarpinian

You can use the SUMIFS() function to only sum values based on criteria. Looking at your screenshot, you might be able to use the service option for something like:

=SUMIFS(]Hours Completed]:[Hours Completed], [Service Option]:[Service Option], CONTAINS("Week", @cell))

This formula would only calculate the Hours Completed column if the Service Option text included the string "Week".

Brandi B.

@Jason Tarpinian Thank you for your help! I must be doing something wrong, I keep getting the #unparseable error.

Jason Tarpinian

Ah, that was my bad, one of my square brackets was backwards. Try this one:

=SUMIFS([Hours Completed]:[Hours Completed], [Service Option]:[Service Option], CONTAINS("Week", @cell))