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!

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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.

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.