COUNTIF question for pulling only particular year

Options

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.

Best Answer

  • William Meixner
    Answer ✓
    Options

    @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

  • William Meixner
    edited 02/07/22
    Options

    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
    JayTeeDee ✭✭
    edited 02/07/22
    Options

    @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
    Answer ✓
    Options

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

    Thank you for the options @William Meixner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!