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.
Best 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.
Answers
-
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)
-
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.
-
@Jason P Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!