COUNTIF Only if System Generated Date Column is Current Year

Overachievers Alumni
edited 12/16/19

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!!

• Overachievers Alumni

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

• ✭✭✭✭✭✭

Excellent!

Happy to help!

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

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.

• 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

• ✭✭✭✭✭✭

@Monica Gallegos Try this...

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

• Thanks Paul! As usual formula worked! Appreciate it!

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭✭✭✭✭

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

Let me know if I can help with anything else!

Best,

Andrée

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.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

@Tdillon Try something like this:

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!