Checking multiple columns if true then return a value by doing this calculation

Hello,

I am having trouble with this formula. Your input is very much appreciated.

I have two columns I am checking if situation is true and if true, then i want to calculate the %complete against all tasks. Here are my columns

my current formula:

=IF(AND([Compliance Validation]@row = true, [% Complete]@row = "Complete"), COUNTIF([% Complete]:[% Complete], "Complete") / COUNT(Standard:Standard), 0)

Output I receive is a value of 20.69% which is not entirely correct.

Because the ask is to compute overall %complete if the Compliance Validation column is checked AND %Complete column is marked Complete but looks like the formula is only checking %Complete column for Complete and dismissing if the check box is checked also. Formula should not add the rows that are not checked into calculating overall % complete of the tasks (standard).

I tested by unchecking the check boxes, and I still receive same value 20.69%. See below.

Appreciate any help on my formula.

Find more posts tagged with

Accepted answers

DKazatsky2

Hi @Ari_Ari,

See if this gets you what you are looking for.

=IF(AND([Compliance Validation]@row = true, [% Complete]@row = "Complete"), COUNTIFS([% Complete]:[% Complete], "Complete", [Compliance Validation]:[Compliance Validation], true) / COUNT(Standard:Standard), 0)

Hope this helps,

Dave

All comments

DKazatsky2

Hi @Ari_Ari,

See if this gets you what you are looking for.

Hope this helps,

Dave

Ari_Ari

@DKazatsky2 Thank you so much! This works just like it should. I see you used CountIFS and added the checkbox condition again.

Thank you.

Ari_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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.