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

Lori Flanigan

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

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.