Formula COUNTIFS, AND, DATE help?

Marie Toft
Marie Toft ✭✭✭
edited 07/18/22 in Formulas and Functions

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

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Marie Toft

    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)),""))))

  • Marie Toft
    Marie Toft ✭✭✭
    Answer ✓

    @Leibel Shuchat

    Thank you! That totally fixed my issue. Love this community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!