Where do I put the IFERROR function to correct for year?
I have a column of dates. New rows are being added over time.
I count how many occur in each month.
In a separate metric sheet, have 12 formulas, 1 for each month, that look like this.
=COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1, {Date Column}, MONTH(@cell) = 1) for January
=COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 2, {Date Column}, MONTH(@cell) = 2) for February, etc.
However, now that it is a new year, I want to exclude 2023 dates so I think I need to add an IFERROR and a YEAR(TODAY()) function,
Something like IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
but I am getting #unparseble.
Your insights would be greatly appreciated, my Smartsheet Community friends!
Stay Safe!
Carroll
Best Answer
-
You can simplify the original COUNTIFS by getting rid of the second range/criteria set as it is redundant of the first.
=COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1
, {Date Column}, MONTH(@cell) = 1)=COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1)
Then you can use an AND statement to add the YEAR argument like so:
=COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
Trying to add exactly what you have in your original post of
IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
has one too many closing parenthesis at the end.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You can simplify the original COUNTIFS by getting rid of the second range/criteria set as it is redundant of the first.
=COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1
, {Date Column}, MONTH(@cell) = 1)=COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1)
Then you can use an AND statement to add the YEAR argument like so:
=COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
Trying to add exactly what you have in your original post of
IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
has one too many closing parenthesis at the end.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks for the speedy reply, Paul. It worked!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 297 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!