Need to add additional Criteria to Metric Formula

I have a Metrics Sheet (named "Escalation Metrics") where I could up all my metrics (most are words rather than numbers. so this allows me to easily create a dashboard based on data that does not contain numbers. My current formula is:

=COUNTIF({Type of Escalation}, [Metric Name]@row)

And I want to add the criteria that the "Status" must be "Open". When I try to add this to the end:

, {Status}, ="Open") I get error messages. That last portion was taken from an Aging metric, which requires one of the criteria to be that the status is Open.

Thanks in advance!!!!!

Sherry Fox

Data Science & Reporting Specialist | PA Performance & Data Insights

UnitedHealth Group | OptumRx

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Best Answer

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Answer ✓

    @Paul Newcome ,

    I have been re-doing the formula since I last replied. It finally worked!!!!! My corrected formula is:

    =COUNTIFS({Type of Escalation}, [Metric Name]@row, {Status}, ="Open")

    The previous one I did that resulted in Unparsable was the following. I have bolded the ")" which was the difference and caused the problem. Thanks for your help!!!!!

    =COUNTIFS({Type of Escalation}, [Metric Name]@row), {Status}, ="Open")

    Sherry Fox

    Data Science & Reporting Specialist | PA Performance & Data Insights

    UnitedHealth Group | OptumRx

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Did you update the function to a COUNTIFS (with the "S" on the end) when you added the new range/criteria set?

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    edited 12/02/22

    @Paul Newcome ,

    No, I had not (duh!). I just tried, and changed my formula to:

    =COUNTIFS({Type of Escalation}, [Metric Name]@row), {Status}, ="Open")

    And the result is Unparsable. It still will not work properly.

    Sherry Fox

    Data Science & Reporting Specialist | PA Performance & Data Insights

    UnitedHealth Group | OptumRx

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Answer ✓

    @Paul Newcome ,

    I have been re-doing the formula since I last replied. It finally worked!!!!! My corrected formula is:

    =COUNTIFS({Type of Escalation}, [Metric Name]@row, {Status}, ="Open")

    The previous one I did that resulted in Unparsable was the following. I have bolded the ")" which was the difference and caused the problem. Thanks for your help!!!!!

    =COUNTIFS({Type of Escalation}, [Metric Name]@row), {Status}, ="Open")

    Sherry Fox

    Data Science & Reporting Specialist | PA Performance & Data Insights

    UnitedHealth Group | OptumRx

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    Glad you got that parenthesis sorted.

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @Paul Newcome ,

    Just like with Excel, often the issue in "formula grammar". It took forever, but I would NOT give up! Thanks again for your help, greatly appreciated.

    Sherry Fox

    Data Science & Reporting Specialist | PA Performance & Data Insights

    UnitedHealth Group | OptumRx

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!