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.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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)

Ari_Ari

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.