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

Options
✭✭✭✭✭

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.

Lori

Tags:

• ✭✭✭✭✭
edited 05/25/22
Options

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!

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

ROLLUP-QualifyDate

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

In that case you would replace the

ISBLANK(@cell)

with

@cell = TODAY(-7)

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.