nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Welcome!

Calculating Time

Ann Alexandrowicz

Good Afternoon,

We are looking to figure out if it is possible to caculate time in Smart sheets. But we need to be able to figure out times for those items checked vs not checked. Is there a way to do this without sorting the sheet out?

Thanks.

Find more posts tagged with

Comments

Chris McKay

Hi Ann,

=SUMIF(range, criterion, [sum_range]) will do what you need. Based on your example, this would look like:

=SUMIF([Executive Session]:[Executive Session], 1, Minutes:Minutes). So the result based on the above would be 5.

Ann Alexandrowicz

Chris,

Thank You!

Follow up question. So I set that as a new row in my sheet and I made a second row to divide by 60 to figure out how many hours.

I put your formula in for Executive Session Meeting and it gave me the total, 435 minutes.

Then in the next row I put in =SUM(Minutes55) / 60

Then this screens shot is the answer I got. How do I turn that into a number of hours?

Thanks.

Chris McKay

Hi Ann,

You are more than welcome.

The reason you're getting an error is that we've previously set a range and sum_range that includes the entire columns. That means your new row will also be included.

Essentially you're creating a loop (circular reference) as the =SUMIF([Executive Session]:[Executive Session], 1, Minutes:Minutes) formula is trying to read the value in Executive Session and Minutes for Row 55, while row 55 is simultaneously trying to calculate a value (from row 54) that actually includes its own value.

It's all very confusing and Smartsheet is throwing out an error.

To get around this, I'd suggest moving your total formulas to the first two rows in another column (call it Totals) or even a new summary sheet. Your hours formula can also be simplified by removing SUM (i.e. =Totals2 / 60), as you're not really summing anything.

Sign in to join the conversation!

Quick Links

Trending discussions

Can't access issue submission
I have run into an interesting but small bug. When setting up an automation, the action 'change cell value' will allow you to select checkbox columns, but not checkbox columns that are the star or flag visual variant. Those can't be selected in the change value action. Now the real issue. I go to submit a bug report or…
Setting up the welcome and help screens.
Hello All, I am having difficulties uploading my information to the welcome and help screens. We are a Micro Soft 365 shop, using share point, and I am getting the error message that share point in refusing to connect. Whereas this appears to be a share point issue; I have reached out to our IT Department, and we cannot…
Contact List validation
Hi Everyone, I am managing a pipeline tracking sheet, and there's one intake form and one dynamic view connecting with this sheet. For the column "Branch Contact" and "Sales leader", I set the filed type as "Contact list" and the validation as "No validation". Once I input the username in the intake form, I go to the…