Using COUNTIFS and Dates

Answers

  • sholmes
    sholmes ✭✭✭✭

    I am trying to use countifs to tell me if a specific person is listed and the other column which is a date column is greater than a certain day then give me a count. Any suggestions?

    Here is what I have tried:

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    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 your answer solved my issue. THANK

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!