Formula COUNTIFS, AND, DATE help?
I'm having trouble getting this formula to work trying to return a sum of classes based on timing (Quarterly). I'm getting the #unparseable error.
I did get it to work for one quarter:
=IF(Today@row = "Q2", COUNTIFS({Grad Date}, AND(IFERROR(MONTH(@cell), 0) > 3, IFERROR(MONTH(@cell), 0) <= 6), {Grad Date}, IFERROR(YEAR(@cell), 0) = Year@row))
But the below formula is not working for me:
=IF(Today@row = "Q1", COUNTIFS({Grad Date}, IFERROR(MONTH(@cell), 0) <= 3, {Grad Date}, IFERROR(YEAR(@cell), 0) = Year@row)), IF(Today@row = "Q2", COUNTIFS({Grad Date}, AND(IFERROR(MONTH(@cell), 0) > 3, IFERROR(MONTH(@cell), 0) <= 6), {Grad Date}, IFERROR(YEAR(@cell), 0) = Year@row)), IF(Today@row = "Q3", COUNTIFS({Grad Date}, AND(IFERROR(MONTH(@cell), 0) >6, IFERROR(MONTH(@cell), 0) <=9), {Grad Date}, IFERROR(YEAR(@cell), 0) = Year@row)), IF(Today@row = "Q4", COUNTIFS({Grad Date}, AND(IFERROR(MONTH(@cell), 0) >9, IFERROR(MONTH(@cell), 0) <=12), {Grad Date}, IFERROR(YEAR(@cell), 0) = Year@row))
Best Answer
-
Thank you! That totally fixed my issue. Love this community.
Answers
-
There were some missing AND functions, as well as a few extra AND functions.
See below:
=IF(Today@row = "Q1", COUNTIFS({Grad Date}, and(IFERROR(MONTH(@cell), 0) <= 3, IFERROR(YEAR(@cell), 0) = Year@row)), IF(Today@row = "Q2", COUNTIFS({Grad Date}, AND(IFERROR(MONTH(@cell), 0) > 3, IFERROR(MONTH(@cell), 0) <= 6,IFERROR(YEAR(@cell), 0) = Year@row)), IF(Today@row = "Q3", COUNTIFS({Grad Date}, AND(IFERROR(MONTH(@cell), 0) >6, IFERROR(MONTH(@cell), 0) <=9,IFERROR(YEAR(@cell), 0) = Year@row)), IF(Today@row = "Q4", COUNTIFS({Grad Date}, AND(IFERROR(MONTH(@cell), 0) >9, IFERROR(MONTH(@cell), 0) <=12, IFERROR(YEAR(@cell), 0) = Year@row)),""))))
-
Thank you! That totally fixed my issue. Love this community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!