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

Lori Flanigan · 05/24/22

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 · 05/25/22

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 · 05/25/22

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

Paul Newcome · 05/25/22

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

Lori Flanigan · 05/25/22

ROLLUP-QualifyDate

Paul Newcome · 05/25/22

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

Lori Flanigan · 05/25/22

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

Paul Newcome · 05/25/22

In that case you would replace the

ISBLANK(@cell)

with

@cell = TODAY(-7)

Lori Flanigan · 05/25/22

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 · 05/25/22

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.

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

Answers

Categories