COUNTIFS with OR and ISBLANK to find number of blank cells 7 days ago

Hi,

I have a formula that is working to count the number of active employees in Role 1, Role 2, Role 3, and Role 4 who did not qualify because the date field is blank.

=COUNTIFS({ROLLUP-EmpStatus}, "Active", {ROLLUP-Role}, OR(@cell = "Role 1", @cell = "Role 2", @cell = "Role 3", @cell = "Role 4"), {ROLLUP-QualifyDate}, ISBLANK(@cell))

I want to update the formula to give me the count of the same information but from 7 days ago. Wherever I've added , <TODAY(-7)), I get errors.

Thanks in advance for your help!

Lori

Find more posts tagged with

Formulas

Accepted answers

All comments

Christian Graf

Hello @Lori Flanigan,

I believe you have the date function correct.

=TODAY(-7)

Maybe something like this would work:

=COUNTIFS({ROLLUP-EmpStatus}, "Active", {ROLLUP-Role}, OR(@cell = "Role 1", @cell = "Role 2", @cell = "Role 3", @cell = "Role 4"), {ROLLUP-QualifyDate}, AND(ISBLANK(@cell), <TODAY(-7)))

You would then have to ensure "ROLLUP-QualifyDate" is a range that encapsulates a column with a type of date.

Hope this Helps!

Lori Flanigan

Thanks, Christian. I've tried that after the ISBLANK(@cell) and it's not working.

Paul Newcome

What column would house the date that you want to compare to TODAY(-7)?

Lori Flanigan

ROLLUP-QualifyDate

Paul Newcome

Would you want to include blank cells, or just those that have a date within the past 7 days?

Lori Flanigan

I want the count of only blank date cells; a different formula is working to count the cells with dates.

Paul Newcome

In that case you would replace the

ISBLANK(@cell)

with

@cell = TODAY(-7)

Lori Flanigan

I believe that gives me the counts of the cells with dates, and I want the count of the cells without dates (blank cells) from the previous week (7 days ago).

Paul Newcome

So you want to count how many cells did not have a date 7 days ago even if there are dates in them now?

In that case you would need to set up an additional date type column and a record a date automation to capture the date when that first date is filled in.

Then you can run the COUNTIFS on this new column.

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.