sum of hours based on weeknumber

john munro

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

john munro

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

Nick Korna

Hi @john munro ,

As you already have it, use your week number column in your SUMIF instead:

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

Example:

Hope this helps, any questions then just ask! 😊

john munro

just tried that

but get this result

john munro

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

john munro

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.

john munro

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

john munro

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)