COUNTIF using Less than Today

Diane Moore

I am struggling with a formula that is trying to count the Record Type entries in the reference sheet that meet the following conditions:

Record Type = "TEST"

Deadline is less than TODAY

Assigned ISBLANK

I tried many variations of the following formula but it keeps erroring out with INVALID OPERATION. When I parse it out, it works until I add the less than today criteria at the end.

=COUNTIFS({Record Type}, {Record Type} = "TEST", {Assigned}, ISBLANK(@cell), {Deadline} < TODAY())

Note: all conditions are in the same reference sheet and I confirmed that the {Deadline} column is a Date type.

TIA!

Genevieve P.

Hi @Diane Moore

The way a COUNTIFS works is that you list the range or the column, then a comma, then the criteria. It looks like you have all the building blocks correct, we just need to adjust the formatting!

Try this:

=COUNTIFS({Record Type}, @cell = "TEST", {Assigned}, ISBLANK(@cell), {Deadline}, @cell < TODAY())

In your criteria, I used @cell to indicate that in the previously stated range, we want to check each cell for the criteria of "Test" or TODAY.

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Diane Moore

The way a COUNTIFS works is that you list the range or the column, then a comma, then the criteria. It looks like you have all the building blocks correct, we just need to adjust the formatting!

Try this:

=COUNTIFS({Record Type}, @cell = "TEST", {Assigned}, ISBLANK(@cell), {Deadline}, @cell < TODAY())

In your criteria, I used @cell to indicate that in the previously stated range, we want to check each cell for the criteria of "Test" or TODAY.

Let me know if this works for you!

Cheers,

Genevieve

Diane Moore

@Genevieve P. Thank you. This worked perfectly.