COUNTIFS Fomula

Options

I created a COUNTIFS formula in Excel which would count the values "Expired" and "Overdue" and add the total's together.

Excel Formula: =SUM((COUNTIFS(C:C, "CO-CURRIC-COI",J:J,"Expired")),(COUNTIFS(C:C, "CO-CURRIC-COI",J:J,"Overdue")))

When trying to emulate it in Smartsheet, I get a #unparseable error. Not sure what I am doing wrong and if there is a better formula to achieve it, I'm all for it.

SmartSheet Formula: =SUM((COUNTIFS({Chancellor's Office - Presidential Evaluat Range 3}, CO-CURRIC-COI), {Chancellor's Office - Presidential Evaluat Range 1,"Expired",),(COUNTIFS({Chancellor's Office - Presidential Evaluat Range 3},CO-CURRIC-COI,{Chancellor's Office - Presidential Evaluat Range 1},"Overdue"))

Best Answer

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    Answer ✓
    Options

    It looks like the syntax in your formula is causing the issue. There are missing "" and extra commas and parenthesis. Try the formula below.

    =SUM(COUNTIFS({Chancellor's Office - Presidential Evaluat Range 3}, "CO-CURRIC-COI", {Chancellor's Office - Presidential Evaluat Range 1}, "Expired"), COUNTIFS({Chancellor's Office - Presidential Evaluat Range 3}, "CO-CURRIC-COI", {Chancellor's Office - Presidential Evaluat Range 1}, "Overdue"))

Answers

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    Answer ✓
    Options

    It looks like the syntax in your formula is causing the issue. There are missing "" and extra commas and parenthesis. Try the formula below.

    =SUM(COUNTIFS({Chancellor's Office - Presidential Evaluat Range 3}, "CO-CURRIC-COI", {Chancellor's Office - Presidential Evaluat Range 1}, "Expired"), COUNTIFS({Chancellor's Office - Presidential Evaluat Range 3}, "CO-CURRIC-COI", {Chancellor's Office - Presidential Evaluat Range 1}, "Overdue"))

  • Fernando Leal
    Options

    Jason. My hero! Thank you. Of course it would be something simple like that. Appreciate your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!