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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!