Countif completed last week
I am trying to write a formula that shows volume of work completed last week. I am getting a result, however it is not grabbing the full list of data. I have played around with different day ranges but it doesn't seem to change my result. The formula I have references other sheets. Please see attached images. I have added a filter to the source sheet to show what should be captured.
=COUNTIFS({Completed Files Focus Tracker Range 1}, [Assigned to]@row, {Completed Files Focus Tracker Range 2}, "Initial", {Completed Files Completed week}, =WEEKNUMBER(TODAY(-1)))
Best Answers
-
Did you try updating the TODAY() function as @Amanda Alv suggested?
TODAY(-1) says "yesterday"
it sounds like you may be looking for last week, which would be subtracting 1 from Today's Weeknumber (instead of directly from today).
WEEKNUMBER(TODAY()) -1 < after closing ))
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Ah! That's super helpful, thank you!
So INVALID REF means that there's something going on with {these references}
Can you first check each one of these to make sure it's looking at the correct column:
- {Completed Files Focus Tracker Range 1}
- {Completed Files Focus Tracker Range 2}
- {Completed Files Completed week}
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi,
Two things to look into. On your reference sheets, are you selecting the column headers, to ensure any newly added data gets included in your formulas?
Second, I believe your "Weeknumber" formula needs to be updated to =WEEKNUMBER(TODAY()) -1) in order for it to take a full week from today. So this assumes it is always run the Monday-Sunday after the actual week of data you are calculating.
Let me know if you have any questions, hope that helps! If that doesn't do it we might need to dig in more to the final part of the full formula. On it's own, the Weeknumber portion I provided would work.
-
Hello,
Yes, my references are based on the column header to capture any new data. These numbers have updated as volumes rise, but they are still not collecting complete data. For instance I am getting a combined 10 for user Krista, but she is actually at 16.
-
Is your Assigned To column in the source sheet a Multi Select column?
If so, you'll want to use the HAS function to see if a cell has the Assigned To value, even if it's selected with others.
Try:
=COUNTIFS({Completed Files Focus Tracker Range 1}, HAS(@cell, [Assigned to]@row), {Completed Files Focus Tracker Range 2}, "Initial", {Completed Files Completed week}, =WEEKNUMBER(TODAY(-1)))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. They are single select only columns. I did try adding the HAS function incase something was changed to allow for multi select, but my values did not change. I feel this should be a very easy formula so I am not sure why it isn't working correctly.
-
Did you try updating the TODAY() function as @Amanda Alv suggested?
TODAY(-1) says "yesterday"
it sounds like you may be looking for last week, which would be subtracting 1 from Today's Weeknumber (instead of directly from today).
WEEKNUMBER(TODAY()) -1 < after closing ))
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. Actually, while the formula seemed to be working well yesterday, it has now become inaccurate again. Second, if I move the -1 completely out of the (Today()) as shown, the formula comes back as invalid.
-
My apologies, I just realized we don't have anything to compare the value to - you'll need to look for if the weeknumber of the cell in that column = the weeknumber of last week:
WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1
=COUNTIFS({Completed Files Focus Tracker Range 1}, [Assigned to]@row, {Completed Files Focus Tracker Range 2}, "Initial", {Completed Files Completed week}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. that is coming back as unparseable I tried playing with it to look at different references from the source sheet and could not get those to work either
-
Did you re-create it in your sheet or Copy/Paste? Can you post a screen capture of your sheet with the formula open?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
-
It looks like you may just have an extra closing parentheses at the end! Try removing it out so you're ending with one )
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. sorry, i did try doing that yesterday as well. It comes back as an invalid reference
-
Ah! That's super helpful, thank you!
So INVALID REF means that there's something going on with {these references}
Can you first check each one of these to make sure it's looking at the correct column:
- {Completed Files Focus Tracker Range 1}
- {Completed Files Focus Tracker Range 2}
- {Completed Files Completed week}
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. ooffda that unearthed the problem(s) caused by me just not thinking clearly. One of the references was not actually a reference at all but typed text, the weeknumber reference was pointing to my completed week number helper column instead of the date completed column so it was not returning a result. Thank you :)
-
Aha! Well-found! I'm glad we got there in the end. 😊
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!