Countifs using greater than w/ date

sholmes

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"))

Jeff Reisman

@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))

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319948#Comment_319948

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})))

Jeff Reisman

@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))

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

I got it to work, thanks again @Jeff R

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319948#Comment_319948

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})))

sholmes

Thank you!