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

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

All comments

Paul Newcome

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

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

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

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

Thanks! This still returns 51

Paul Newcome

Ok. What about a screenshot of your filter settings?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.