Sheet Summary Formula - All Completed Tasks in the last 30 days

roxBVL · 10/20/23

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

Paul Newcome · 10/20/23

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?

roxBVL · 10/27/23

https://community.smartsheet.com/discussion/comment/401031#Comment_401031

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

Paul Newcome · 10/27/23

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

roxBVL · 10/27/23

https://community.smartsheet.com/discussion/comment/402046#Comment_402046

Thanks! This still returns 51

Paul Newcome · 10/27/23

Ok. What about a screenshot of your filter settings?

Sheet Summary Formula - All Completed Tasks in the last 30 days

Answers

Help Article Resources

Categories