Using COUNTIFS and Dates

System

This discussion was created from comments split from: Newbie: trying to return cell contents if date is less than and greater than....

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

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

jmcallister

Jeff your answer solved my issue. THANK

Jeff Reisman

https://community.smartsheet.com/discussion/comment/384236#Comment_384236

Excellent!