Count the number of lines, based on the creation date column
Hi,
Can anyone help me with the formula? I have a sheet with approx 400 lines of which the creation date is automatically generated.
I would like to count how many lines were created in the year of 2020, 2021 and 2022 based on that column.
Thank you for helping me out!
Best Answers
-
Hi @kinnari s.
I hope you're well and safe!
To add to Paul's excellent advice/answer.
Try something like this.=COUNTIFS([Creation Date]:[Creation Date]; IFERROR(YEAR(@cell); 0) = 2021)
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Kinnari S. Give this modification a try:
=COUNTIFS([Creation date]:[Creation date]; IFERROR(YEAR(@cell); 0) = 2021)
This will account for any blanks and/or non-date values within the range.
Answers
-
You would use something like this:
=COUNTIFS([Creation Date]:[Creation Date], YEAR(@cell) = 2021)
-
Thank you, it makes sense, but I don't know what to refer to in "@cell"; because I want to count all of the lines created in that year.
=COUNTIFS([Creation date]:[Creation date]; YEAR([Creation date]:[Creation date]); "2021")
=> Incorrect argument set
=COUNTIFS([Creation date]:[Creation date]; YEAR([Creation date]:[Creation date]; "2021"))
=> Invalid data type
-
You would leave the "@cell" as is. It basically tells the formula to look at the Creation Date column and evaluate the YEAR on a cell by cell basis.
-
Thank you, It's still not working, I've tried several combinations.
=COUNTIFS([Creation date]:[Creation date]; YEAR(@cell) = 2021)
=> Invalid data type
I did create a workaround, where I created a new column using the "YEAR()"-formula. This works, so the formula does work, but apparently not in combination with the "COUNTIFS"-formula.
-
Hi @kinnari s.
I hope you're well and safe!
To add to Paul's excellent advice/answer.
Try something like this.=COUNTIFS([Creation Date]:[Creation Date]; IFERROR(YEAR(@cell); 0) = 2021)
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
At mention didn't work in my first answer, so I added another one so you get the notification.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Kinnari S. Give this modification a try:
=COUNTIFS([Creation date]:[Creation date]; IFERROR(YEAR(@cell); 0) = 2021)
This will account for any blanks and/or non-date values within the range.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!