Summary Sheet Formula Countifs: Next 60 Days
Smartsheet Community:
I am not Sure why my Formula is Not Counting Correctly, but when I use the Filters I get 12 as the Count. See Below:
However, my Sheet Summary Formula Results Count 1. See Formula Below:
=COUNTIFS(Status:Status, "2-In Process", [Finish Date (Plan)]:[Finish Date (Plan)], >TODAY(60))
I Flip My Greater than Less Than Symbol to see if that was my issue and the Results are 19.
Thanks for your Help in Advance
Best Answer
-
Which one is accurate? 😂 Perhaps the "in the past (days)" part of the filter includes today's date, making it effectively TODAY(-59)? To test this, take the equal sign off of >=TODAY(-60) and see if you get a count of 4.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
@Kaleb Try less than or equal to (<=) along with another criteria specifying greater than today.
=COUNTIFS(Status:Status, "2-In Process", [Finish Date (Plan)]:[Finish Date (Plan)], <= TODAY(60), [Finish Date (Plan)]:[Finish Date (Plan)], > TODAY(0))
You need to add the greater than Today in there because the filter, as part of "is in the next (days)", includes the logic that you also mean, "not before today, not today, but in the (days) after today."
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman That Worked! So the Last 60 Days from Today (Past), I just need to Flip the Greater Than Less Than Symbols, Correct?
-
I tweaked the Formula for the Last 60 Days Like the Filter and the Filter Results is 4:
The Formula Result is 0. The Only Changes I Made was to the Status to Complete, Used Finish Date Actual Column, and Flipped the Symbols for Less than 60, but the Results were 0.
I'm Scratching my Head because it should be the Same Logic but Less Than 60 Days.
-
You have to switch to >= TODAY(-60) and < TODAY(0). You need the negative 60 (-60) in there to specify 60 days in the past.
=COUNTIFS(Status:Status, "2-In Process", [Finish Date (Plan)]:[Finish Date (Plan)], >= TODAY(-60), [Finish Date (Plan)]:[Finish Date (Plan)], < TODAY(0))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Ok I made the Changes but the Result is 7 and the Filter with that Same Criteria is 4
Formula:
=COUNTIFS(Status:Status, "5-Complete", [Finish Date (Actual)]:[Finish Date (Actual)], >=TODAY(-60), [Finish Date (Actual)]:[Finish Date (Actual)], <TODAY(0))
-
Which one is accurate? 😂 Perhaps the "in the past (days)" part of the filter includes today's date, making it effectively TODAY(-59)? To test this, take the equal sign off of >=TODAY(-60) and see if you get a count of 4.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Your Formula is Right. I did not Change my Filter for Finish Date (Actual) 😂 SMH
Thanks for All Your Help! I Really Appreciate it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
