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
-
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!
-
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
-
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!
-
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?
-
I got it to work, thanks again @Jeff R
-
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!
-
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!