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
Business Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
Best Answer
-
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 Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
Answers
-
Did you update the function to a COUNTIFS (with the "S" on the end) when you added the new range/criteria set?
-
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 Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
-
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 Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
-
Happy to help. 👍️
Glad you got that parenthesis sorted.
-
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 Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!