How to COUNTIFS when cell is blank and not today.

Options

I need to know how to return a value by these parameters.

  1. There is a column named Completion Date. If it is blank, I need it counted.
  2. I need it to look at another column named Service Date and count if that date is in the past (not today or in the future).
  3. So, I just need it counted if it is blank and earlier than today.

=COUNTIFS([Completion Date]:[Completion Date], ISBLANK(@cell), AND([Service Date]:[Service Date], < TODAY(@cell)))

I keep getting a #UNPARSEABLE error.

Please help.

Thank you

Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Justin Mauzy ,


    You're close! Try this:

    =COUNTIFS([Completion Date]:[Completion Date], ISBLANK(@cell), [Service Date]:[Service Date], < TODAY(@cell))

    The AND is not needed - COUNTIFS includes an understood AND in it. :)


    Hope this helps!

    Best,

    Heather

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Justin Mauzy

    Hope you are fine, please try the following formula:

    =COUNTIFS([Completion Date]:[Completion Date], ISBLANK(@cell), [Service Date]:[Service Date], @cell < TODAY())

    the following screenshot show the sample


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    @Bassam.M Khalil Nice catch! I didn't notice the @cell in the TODAY part.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    @Heather D

    Thanks for the courtesy, we are one team working together to help the SmartSheet community.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers