Count Compliant Status Reporting

Hello Community,

I am pretty stumped on this one; I want to count the number of rows (projects) that are in and out of compliance for their status reporting ("Status Date") based on their project stage ("Project Stage") as different items in the Sheet Summary.

Currently, "Project Stage" has multiple possible stages, but I'm just looking to count items that are "Execution". And, as we're in process of launching this master project list, the "Status Date" column currently contains both dates and words though is set as a date column (could this be the problem?).

I think I have the formula working for the in compliance reporting: =COUNTIFS([Project Stage]:[Project Stage], "Execution", [Status Date]:[Status Date], >=TODAY(-7))

But I can't seem to get the out of compliance formula to work (not getting expected result). I currently have it as: =COUNTIFS([Project Stage]:[Project Stage], "Execution", [Status Date]:[Status Date], <TODAY(-7))

Any thoughts? Thanks in advance!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Yes. We would use something like this:

=COUNTIFS([Project Stage]:[Project Stage], "Execution", [Status Date]:[Status Date], OR(@cell < TODAY(-7), ISTEXT(@cell))

This will count rows where the date is more than 7 days in the past as well as rows where the date column contains some kind of text.

All comments

Paul Newcome

The one that is giving you the unexpected count is counting all rows where the project stage is currently equal to "Execution" and the status date is more than 7 days in the past.

If that is in fact the logic you wanted, try applying a filter to the sheet that mimics the formula range/criteria sets and see how many rows the filter pulls in sheet. Is that the same number?

Claire LD Schulz

Interesting test, Paul! No, I get a 13 rows through the filter (which is the expected value), but I only am getting a result of 1 with the formula (need the correct result in the formula for other calculations, can't rely on a filter count).

Any other ideas??

Paul Newcome

Are you able to provide a screenshot of the filter? It sounds to me like the formula may not be written to exactly match what you are needing.

Claire LD Schulz

Paul Newcome

Ok. So your formula is looking for dates that are more than 7 days in the past, but the filter is only excluding dates that are not in the last 7 days. This means that the filter will also show rows that are in the future.

To get the filter to mimic the formula, you would need to add another argument for where the Status Date is in the past.

To get the formula to mimic the filter, you would end up with something more like this:

=COUNTIFS([Project Stage]:[Project Stage], "Execution", [Status Date]:[Status Date], OR(@cell < TODAY(-7), @cell >= TODAY()))

So what we need to do is figure out exactly what should be included in the count. Are you wanting to pull all dates that are not within the past 7 days and include future dates or are you looking to exclude future dates? Excluding future dates should be the second formula from your original post.

=COUNTIFS([Project Stage]:[Project Stage], @cell = "Execution", [Status Date]:[Status Date], @cell < TODAY(-7))

Claire LD Schulz

Humm, I don't think that is quite it (but I am SO appreciating this dialogue). As this is a status update column, there will never be future dates (can't report on the future, we don't have a crystal ball :) ). All dates in that column are either today or in the past (though, currently some have the word "need update" in them as they are new, but the filter still captures them ...)

Paul Newcome

Are you wanting the formula to grab those as well? Your formula is only going to grab dates and will not count "need update".

Claire LD Schulz

oooohhh, yes, I think we're close! Is there a way to capture any text (so if it says something different than "need update" it would still be counted too?)?

Paul Newcome

Yes. We would use something like this:

=COUNTIFS([Project Stage]:[Project Stage], "Execution", [Status Date]:[Status Date], OR(@cell < TODAY(-7), ISTEXT(@cell))

This will count rows where the date is more than 7 days in the past as well as rows where the date column contains some kind of text.

Claire LD Schulz

YAS, Paul! This worked! Thank you so much! Really appreciate the brainpower on this one.

Paul Newcome

Happy to help. 👍️

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.