Summary Sheet Formula Countifs: Next 60 Days

Kaleb

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/331680#Comment_331680

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.

Jeff Reisman

@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."

Kaleb

@Jeff Reisman That Worked! So the Last 60 Days from Today (Past), I just need to Flip the Greater Than Less Than Symbols, Correct?

Kaleb

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.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/331660#Comment_331660

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

Kaleb

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/331680#Comment_331680

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.

Kaleb

@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.