sum of hours based on weeknumber
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))
Best Answer
-
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
Answers
-
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! 😊
-
just tried that
but get this result
-
I made a dummy smartsheet like yours and it works, now to figure out why it isn't working on my smartsheet
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!