# Count the number of lines, based on the creation date column

Options
edited 02/20/23

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!

Tags:

• ✭✭✭✭✭✭
Options

Hi @kinnari s.

I hope you're well and safe!

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

You would use something like this:

=COUNTIFS([Creation Date]:[Creation Date], YEAR(@cell) = 2021)

• Options

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

• ✭✭✭✭✭✭
Options

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.

• edited 02/21/23
Options

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.

• ✭✭✭✭✭✭
Options

Hi @kinnari s.

I hope you're well and safe!

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!