Return a sum using using sumifs and dates

My objective is to return a sum of all 'Work Hours' that are in a department (Wire in this example), that fall within a date range of the 'END' date that Smartsheet is reporting..

This formula returns a value, but I've checked manually, and I've determined the value is incorrect. I've exported a filtered view of the sheet, and sorted by date, and then done a sum of the hours.

Here is the formula I'm using: =SUMIFS([Work Hours]:[Work Hours], Department:Department, "Wire", Finish:Finish, >=DATE(2020, 8, 1), Finish:Finish, <=DATE(2020, 8, 31))

Find more posts tagged with

Manufacturing

Formulas

functionality

Conditional Formatting

Accepted answers

Larry Bejcar

I just want to give a group thanks to both Lewis and Genevieve for their quick responses. In addition, I'd previously asked the question of Smartsheet themselves, and I'm working with their solution at present.

The column we're taking the sums from is subject to the calculations for duration used by Smartsheet in production dependencies. In effect, while the cell reads as 'hours', the math behind it is an 8 hour work day. Long story short, when I multiply my result by 8, I get the totals I' expected.

I'm going to independently explore Lewis's solution, as I explore my use of Smartsheet.

Once again, thanks to all.

All comments

Genevieve P.

Hi @Larry Bejcar

I've tested your formula and it works as expected for me, with the correct result. Is it possible that your hours have decimals, and this is being rounded in the result in the formula?

If so, you may want to select the Column name that the formula is stored in, then click the decimal button in the top toolbar menu to ensure that decimals are being shown:

Screen Shot 2020-08-05 at 11.51.19 AM.png

If that's not the issue, would you mind testing something else for me? Could you create a Report from this sheet, just with these 3 columns & the criteria that's in your formula (the date range & the department).

Then can you click on the Work Hours column? Highlighting the column like this in a Report will give you a quick summary in the bottom right corner:

Screen Shot 2020-08-05 at 11.59.36 AM.png

Let me know what you find out!

Cheers,

Genevieve

Larry Bejcar

I'm going to independently explore Lewis's solution, as I explore my use of Smartsheet.

Once again, thanks to all.

Genevieve P.

Hi Larry,

Thanks for providing more detail and letting us know what you found out!

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.