Formula for Counting by Year

On my Smartsheet, I want to count the number of lots manufactured per year based on the date of manufacture. If the date is between 1/1/2023 and 12/31/2023, give me a value of "2023", and the same moving forward in 2024 and so on. I cannot figure out what the formula will be. Can someone please advise? Thank you in advance.

Tags:

Best Answer

  • Jason P
    Jason P ✭✭✭✭
    Answer ✓

    Hi Dina,

    As a sheet summary. =COUNTIF([Name of your column]:[Name of you column:], IFERROR(YEAR(@cell), 0) = 2023). The IFERROR manages cells that are not dates by counting them as 0.

    As a cross sheet formula. =(COUNTIFS({Name of sheet and column}, AND(IFERROR(YEAR(@cell), 0) = 2023)))

    This is a copy of one we use for simular count, each year I insert another column, copy formula and change the year.

    Hope this helps.

    Cheers.

    Forever forwards Backwards never.

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Dina B

    I would create a field in your sheet summary section and use the following formula.

    =COUNTIF([Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = 2024)

  • Jason P
    Jason P ✭✭✭✭
    Answer ✓

    Hi Dina,

    As a sheet summary. =COUNTIF([Name of your column]:[Name of you column:], IFERROR(YEAR(@cell), 0) = 2023). The IFERROR manages cells that are not dates by counting them as 0.

    As a cross sheet formula. =(COUNTIFS({Name of sheet and column}, AND(IFERROR(YEAR(@cell), 0) = 2023)))

    This is a copy of one we use for simular count, each year I insert another column, copy formula and change the year.

    Hope this helps.

    Cheers.

    Forever forwards Backwards never.

  • Dina B
    Dina B
    edited 02/20/24

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!