Need to add additional Criteria to Metric Formula

Options

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

Business System Data Analyst

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

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

Best Answer

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

    @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

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    @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

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

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

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

    @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

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Happy to help. 👍️


    Glad you got that parenthesis sorted.

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    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!