COUNTIF Only if System Generated Date Column is Current Year

Sara_Cook

I am trying to create a formula in a Sheet Summary field that displays the total number of rows I have in the sheet that were created in the CURRENT YEAR. I have a system-generated date column - the column is titled "Created".

The only criteria is that the Created column has a date and that the date is in the current year.

I cannot figure out how to use a COUNTIF formula to display only the number of rows that have a date in the current year. I've looked through the discussions and have seen different variations, but none of them work.

The system-generated date column has a xx/xx/xx format for the month/day/year - so I wonder if that has something to do with it? I've been trying to use YEAR or DATE in my formulas, but they all seem to want a 4 digit year.

This seems like it would be simple - just count the current year. I'm hoping there is a simple solution that I'm just missing.

Thanks!!

Andrée Starå

Hi Sara,

Try something like this.

=COUNTIF(Created:Created, IFERROR(YEAR(@cell), 0) = 2019)

Use this one for current year.

=COUNTIF(Created:Created, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Andrée Starå

Hi Sara,

Try something like this.

=COUNTIF(Created:Created, IFERROR(YEAR(@cell), 0) = 2019)

Use this one for current year.

=COUNTIF(Created:Created, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Sara_Cook

Hi Andree,

Yes that formula for the Current Year worked - thank you so much! I had tried one similar to that previously, but must have been missing something, so thank you!

I also found in another thread another formula that also works:

=COUNTIFS(Created:Created, IF(ISDATE(@cell), YEAR(@cell)) = YEAR(TODAY()))

I don't know if there is one is better for certain purposes than another, but I'm very happy to have two working formulas! Thank you for your response.

Sara

Andrée Starå

Excellent!

Happy to help!

Yes, there almost always multiple version for the same thing. 😉

Monica Gallegos

Hi Andrée,

Saw this post and found both of these formulas very valuable. I was wondering if you had a suggestion to adjust your current year formula, as show below

=COUNTIF(Created:Created, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

to CountIF for the previous year? But instead of listing the year 2019, if could automatically search previous year without entering in the specific year? I'm trying to use this formula to provide a YTD growth of entries.

Any suggestions would be appreciated.

Thanks, Monica

Paul Newcome

@Monica Gallegos Try this...

=COUNTIF(Created:Created, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1)

Monica Gallegos

Thanks Paul! As usual formula worked! Appreciate it!

Paul Newcome

Happy to help. 👍️

Andrée Starå

@Monica Gallegos

Glad to hear that the formulas are valuable for you as well.

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Trish Dillon

Great info!

How would you do this formula to gather the specific date?

i need to find how many on August 17th,2020 vs August 18th 2020..

Paul Newcome

@Tdillon Try something like this:

=COUNTIFS(Created:Created, DATEONLY(@cell) = DATE(2020, 08, 17))