COUNTIF question for pulling only particular year
Status
Closed 167
InProgress 22
Cancelled 1
=COUNTIF({Data Set 1}, "Closed")
Hello All,
I want to be able to pull/report number for a particular year. For the COUNTIF function above, it is currently pulling all "Closed" items, but would like to be able to change the function so that it only would pull from dates in 2020, 2021, 2022, etc. Thank you.
Best Answer

@JayTeeDee It would depend on how your data is setup  if the column you are searching against is the date data type, and you want to evaluate by year (or across multiple years) then you can try this:
=COUNTIFS({Data Set 1},"Closed",{Date Data Set},IFERROR(YEAR(@cell),0) >= 2021)
This example would return the number of records that are status Closed and with a date that has a year that is greater than or equal to 2021.
If your data is date type and you want to evaluate based on a specific date value (as you had in your example of ">=1/1/2022" then you would need to call the DATE() function to feed the formula the date value to evaluate. As example:
=COUNTIFS({Data Set 1},"Closed",{Date Data Set},>= DATE(2022,1,1))
William
Answers

If I am reading your post correctly, it sounds like you might benefit from the COUNTIFS() function. This function allows for a record to be counted if all the criteria (multiple criteria) are satisfied.
For example:
=COUNTIFS({Data Set 1},"Closed",{Year Data Set},2022)
In the example the records would be counted if they had a status of Closed and were in the year 2022.
Hope that helps,
William
More infor on COUNTIFS: https://help.smartsheet.com/function/countifs

@William Meixner What if I wanted it to be a range of dates for anything in 2022 instead of just 2022? This is the function I came up with but not reporting anything.
=COUNTIFS({Data Set 1},"Closed",{Year Data Set}, ">=1/1/2022")

@JayTeeDee It would depend on how your data is setup  if the column you are searching against is the date data type, and you want to evaluate by year (or across multiple years) then you can try this:
=COUNTIFS({Data Set 1},"Closed",{Date Data Set},IFERROR(YEAR(@cell),0) >= 2021)
This example would return the number of records that are status Closed and with a date that has a year that is greater than or equal to 2021.
If your data is date type and you want to evaluate based on a specific date value (as you had in your example of ">=1/1/2022" then you would need to call the DATE() function to feed the formula the date value to evaluate. As example:
=COUNTIFS({Data Set 1},"Closed",{Date Data Set},>= DATE(2022,1,1))
William

Thank you for the options @William Meixner
Help Article Resources
Categories
Check out the Formula Handbook template!