Sheet Summary Formula - All Completed Tasks in the last 30 days
Good morning,
I am trying to add the total number of closed items within the last 30 days. I have used a lot of the suggested formulas in past posts, but it is still counting ALL completed tasks (72) instead of the tasks completed in the last 30 days (40).
I'm not sure if it's because it's a sheet summary and not within the sheet itself? I am using the following formula, tried it with and without an AND statement.
=COUNTIFS(Status:Status, "Complete", Modified:Modified, <=TODAY(), Modified:Modified, >=TODAY(-30))
Answers
-
Have you applied a filter to the sheet that mimics the ranges and criteria to see how many pop up that way then provide a screenshot of the rows it shouldn't be counting but is?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Sure, this is with a filter of Status = Complete and Modified = in the last 30 days. It returns 6 rows.
The formula returns "51". I'm not sure which 51 it is counting.
Last 30 days (6):
All modified (88):
-
It shouldn't amke a difference, but I have seen this help before. Let's add some @cell references.
=COUNTIFS(Status:Status, @cell = "Complete", Modified:Modified, @cell <=TODAY(), Modified:Modified, @cell >=TODAY(-30))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks! This still returns 51
-
Ok. What about a screenshot of your filter settings?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!