sum of hours based on weeknumber

Options
✭✭✭

I have project hours and dates and this summary formula works well: =SUMIF(PressDate:PressDate, TODAY(), (PressHours:PressHours))

I also have a weeknumber formula on the sheet =weeknumber(PressDate@row) for the weeknumber of the press date and on the summary =WEEKNUMBER(TODAY()) to calculate the weeknumber based on today .

what i am trying to do is create a sumif formula that sums the PressHours based on today's week number.

example formula below which doesn't error but doesn't work either to sum all the presshours based on todays weeknumber which is 9.

=SUMIF(PressDate:PressDate, WEEKNUMBER(TODAY(), PressHours:PressHours))

• ✭✭✭
Options

found the issue, there were items with TBD instead of dates in the PressDate field.

sorry for the noise on this, i should have caught that

• ✭✭✭✭✭✭
Options

Hi @john munro ,

=SUMIF(Weeknumber:Weeknumber, WEEKNUMBER(TODAY()), PressHours:PressHours)

Example:

Hope this helps, any questions then just ask! 😊

• ✭✭✭
Options

just tried that

but get this result

• ✭✭✭
Options

I made a dummy smartsheet like yours and it works, now to figure out why it isn't working on my smartsheet

• ✭✭✭
Options

Thanks Nick for your help, but totally a mystery as to why this won't work on my sheet but does on a test sheet.

• ✭✭✭
Options

found the issue, there were items with TBD instead of dates in the PressDate field.

sorry for the noise on this, i should have caught that

• ✭✭✭
Options

in case any is reviewing this case, to fix the #invalid data type error on weeknumber i changed the weeknumber formula from:=WEEKNUMBER([PressDate]@row)

to

=IFERROR(WEEKNUMBER([PressDate]@row), 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!