COUNTIF question for pulling only particular year

JayTeeDee

Status

Closed 167

In-Progress 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.

William Meixner

@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

William Meixner

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

JayTeeDee

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

William Meixner

@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

JayTeeDee

Thank you for the options @William Meixner