Help with a COUNTIFS Formula that contains OR and Divide to calculate a %

Garry McEwen

Hey Guys

I wonder could i pick your brains to iron out the kinks in my formula

I would like to calculate a % based upon the following references

Sheet Reference {EPIC}

Value "GBT-xxxx"

Sheet reference {JIRA STATUS}

Value "Pass" or "Fail/Issue" or "Gap" or "Pass - Now Fail" or "N/A (Not Applicable)"

Divided by 234 which is the total number of tests in the cycle

I have constructed the following formula but am currently getting unparseable

=COUNTIFS({EPIC}, "GBT-9950", {JIRA STATUS}, OR(@cell = "Pass", @cell ="Fail/Issue", @cell ="N/A Not Applicable", @cell = "Pass - Now Fail"), /234)

Many Thanks

Garry

Find more posts tagged with

Formulas

help

Countif

Accepted answers

Kelly Moore

Hey @Garry McEwen

I think your first formula would have worked after removing the extra comma before the /234 and moving the parenthesis to close your OR statement.

=COUNTIFS({EPIC}, "GBT-9950", {JIRA STATUS}, OR(@cell = "Pass", @cell ="Fail/Issue", @cell ="N/A Not Applicable", @cell = "Pass - Now Fail"))/234

I'm curious - were there any responses in the {JIRA Status} that should not be included?

Does this work for you?

Kelly

All comments

Garry McEwen

@Kelly Moore i'm hoping maybe you could help with this one?

Garry McEwen

Have used AI to fix the formula

=COUNTIFS({EPIC}, "GBT-9950", {JIRA STATUS}, OR(CONTAINS("Pass", @cell), CONTAINS("Gap", @cell), CONTAINS("Fail/Issue", @cell), CONTAINS("Pass - Now Fail", @cell), CONTAINS("N/A (Not Applicable)", @cell), CONTAINS("N/A (Not/Applicable)", @cell))) / 234

but the value still returns 0

Nic Larsen

Try:

=COUNTIFS({EPIC}, "GBT-9950", {JIRA STATUS}, OR(HAS(@cell, "Pass"), HAS(@cell, "Gap"), HAS(@cell, "Fail/Issue"), HAS(@cell, "Pass - Now Fail"), HAS(@cell, "N/A (Not Applicable)"), HAS(@cell, "N/A (Not/Applicable)"))) / 234

If that doesn't work, I would first try to confirm that the total count without the 234 is working. Then you can add that at the end.

Kelly Moore

Hey @Garry McEwen

I think your first formula would have worked after removing the extra comma before the /234 and moving the parenthesis to close your OR statement.

=COUNTIFS({EPIC}, "GBT-9950", {JIRA STATUS}, OR(@cell = "Pass", @cell ="Fail/Issue", @cell ="N/A Not Applicable", @cell = "Pass - Now Fail"))/234

I'm curious - were there any responses in the {JIRA Status} that should not be included?

Does this work for you?

Kelly

Garry McEwen

@Kelly Moore no all were to be included, all sorted now and formula working.

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.