COUNTIF Only if System Generated Date Column is Current Year
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!!
Best Answer
-
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
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.
Answers
-
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
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 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
-
-
Thanks Paul! As usual formula worked! Appreciate it!
-
Happy to help. 👍️
-
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
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!