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

Lori Flanigan
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

Tags:

Answers

  • Christian Graf
    Christian Graf ✭✭✭✭
    edited 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
    Lori Flanigan ✭✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

  • Lori Flanigan
    Lori Flanigan ✭✭✭✭✭✭

    ROLLUP-QualifyDate

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

  • Lori Flanigan
    Lori Flanigan ✭✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    In that case you would replace the

    ISBLANK(@cell)


    with


    @cell = TODAY(-7)

    thinkspi.com

  • Lori Flanigan
    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
    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.

    thinkspi.com