Return a sum using using sumifs and dates

Larry Bejcar

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))

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.

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:

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:

Let me know what you find out!

Cheers,

Genevieve

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.

Genevieve P.

Hi Larry,

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