Formula for Counting by Year

Dina B

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.

Jason P

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.

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

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.

Dina B

@Jason P Thank you so much!