COUNTIF using Less than Today


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.



Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!