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

Kinnari S.

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!

Andrée Starå

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!

Paul Newcome

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

Paul Newcome

You would use something like this:

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

Kinnari S.

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

Paul Newcome

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.

Kinnari S.

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.

Andrée Starå

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!

Andrée Starå

@Kinnari S.

At mention didn't work in my first answer, so I added another one so you get the notification.

Paul Newcome

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