How to COUNTIFS when cell is blank and not today.

Justin Mauzy

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

Heather Duff

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

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

Heather Duff

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

Bassam Khalil

@Heather D

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

Heather Duff

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

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

Heather Duff

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

Bassam Khalil

@Heather D

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

Justin Mauzy

This worked perfectly! Thank you for the quick response.