Countifs using greater than w/ date

I am trying to write a formula to let me know if a column has a certain person in one column with a date missing or greater than a received date of an item.


My attempt so far:

=(COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person Name", {2021 Tax Return Reconciliation Range 2}, <>"2/15/22"))

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @sholmes

    Let's start with the parentheses. You don't need one after the opening equal sign, and that's putting an extra closing parentheses on the end that you don't need. Let's get rid of those.

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", {2021 Tax Return Reconciliation Range 2}, >"2/15/22", {2021 Tax Return Reconciliation Range 2})

    Next, you've got the second range listed twice for some reason. Let's drop that.

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", {2021 Tax Return Reconciliation Range 2}, >"2/15/22")

    Lastly, Smartsheet can be very fickle about date values. If you want to specify a particular date inside a formula (that may not be present in a date cell on your row,) it's usually best to use the DATE function to build it inside the formula, so that Smartsheet can properly recognize it as a date. The DATE function syntax is DATE(year, month, day). Try this out and see how it goes.

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", {2021 Tax Return Reconciliation Range 2}, > DATE(2022, 2, 15))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/17/22 Answer ✓

    Glad it's working for you.

    There are a few ways to look for a blank date field.

    One would be to change the > DATE(2022, 2, 15) to an = "" so that it equals empty quotes.

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", {2021 Tax Return Reconciliation Range 2}, = "")

    Another way is to use the ISBLANK function:

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", ISBLANK({2021 Tax Return Reconciliation Range 2}))

    And a third way is to use the NOT and ISDATE functions:

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", NOT(ISDATE({2021 Tax Return Reconciliation Range 2})))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @sholmes

    Let's start with the parentheses. You don't need one after the opening equal sign, and that's putting an extra closing parentheses on the end that you don't need. Let's get rid of those.

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", {2021 Tax Return Reconciliation Range 2}, >"2/15/22", {2021 Tax Return Reconciliation Range 2})

    Next, you've got the second range listed twice for some reason. Let's drop that.

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", {2021 Tax Return Reconciliation Range 2}, >"2/15/22")

    Lastly, Smartsheet can be very fickle about date values. If you want to specify a particular date inside a formula (that may not be present in a date cell on your row,) it's usually best to use the DATE function to build it inside the formula, so that Smartsheet can properly recognize it as a date. The DATE function syntax is DATE(year, month, day). Try this out and see how it goes.

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", {2021 Tax Return Reconciliation Range 2}, > DATE(2022, 2, 15))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • sholmes
    sholmes ✭✭✭✭

    Awesome, that worked and thanks for explaining the detail. Another related question, if you could. I just realized, I need this same thing but instead of greater than date, need to know the cell is blank. Do I just drop the dates and do parenthesis?

  • sholmes
    sholmes ✭✭✭✭

    I got it to work, thanks again @Jeff R

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/17/22 Answer ✓

    Glad it's working for you.

    There are a few ways to look for a blank date field.

    One would be to change the > DATE(2022, 2, 15) to an = "" so that it equals empty quotes.

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", {2021 Tax Return Reconciliation Range 2}, = "")

    Another way is to use the ISBLANK function:

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", ISBLANK({2021 Tax Return Reconciliation Range 2}))

    And a third way is to use the NOT and ISDATE functions:

    =COUNTIFS({2021 Tax Return Reconciliation Range 1}, "Person", NOT(ISDATE({2021 Tax Return Reconciliation Range 2})))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • sholmes
    sholmes ✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!