Seeking Formula output in ratio when conditions are met in other columns with dropdown selections
Hello,
Here is another formula attempt for below table:
I want to check if the % Complete is "Complete" and Standard is "I  Availability of Services" for the current row, is true, then I need the ratio of rows that meet both conditions to the total number of rows in the Standard column.
Here is my attempt at the formula:
=IF(AND([% Complete]@row = "Complete", (Standard:Standard)@row = "I  Availability of Services"), COUNTIFS([% Complete]:[% Complete], "Complete", (Standard:Standard), "I  Availability of Services") / COUNT(Standard:Standard), 0)
I am getting Unparseable error. What am I missing?
Appreciate the help.
Answers

Your syntax is off.
=IF(AND([% Complete]@row = "Complete", Standard@row = "I  Availability of Services"), COUNTIFS([% Complete]:[% Complete], "Complete", Standard:Standard, "I  Availability of Services") / COUNT(Standard:Standard), 0)

Thank you @Paul Newcome ! This works!
Any ideas on how best to check on the syntax? When I look at the formula example SmartSheet gives you, it is not so straight forward.
Thanks,
Ari
Help Article Resources
Categories
Check out the Formula Handbook template!