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

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 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.