#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Formula Help

Options
edited 12/09/19

Hello,

Can someone please help me with this formula. I don't know what I'm doing wrong. I have 3 auto fail items and it seems that only the PCI one works. If either Solved, Protected, or PCI are scored No, the end score needs to be 0%. Everything is either scored, Yes, No, or Not Applicable.

I have 8 columns:

A, B, C, D, E, Solved, Protected, PCI

I'm using:

=IF([Solved]1 = "No", 0) + IF([Protected]1 = "No", 0) + IF([PCI]1 = "No", 0), (((COUNTIF([A]1:[PCI]1, "Yes") * 1 + (COUNTIF([A]1:[PCI]1, "Not Applicable") * 1))) / 8))

Thank you,

Barb

• ✭✭✭✭✭✭
Options

Barb,

Your formula is imbalanced (has non matching counts of "(" and ")"

I assume your formula is actually located in a 9th column (otherwise the reference to [A]1:[PCI]1 would lead to a circular reference.

Breaking it down:

=IF([Solved]1 = "No", 0)

results in 0 when Solved is "No" but gives a blank when it is not "No".

Same for the next two parts

+ IF([Protected]1 = "No", 0)

+ IF([PCI]1 = "No", 0)

And then that comma looks out of place

, (((COUNTIF([A]1:[PCI]1, "Yes") * 1 + (COUNTIF([A]1:[PCI]1, "Not Applicable") * 1))) / 8))

and the last two ")" are what is causing the imbalance.

.....

So, assuming A,B,C,D,E hold values,

if ANY of the other three are "No" then 0

otherwise, count the "Yes" and "Not Applicables" - equally weighted.

=IF(OR(Solved1 = "No", Protected1 = "No", PCI1 = "No"), 0, "do something")

will check for any "No" in those three columns and return 0 if it finds one (or more)

Otherwise it will print "do something"

Replace the "do something" with what you want like

5 - COUNTIF(A1:E1,"No")

will assume that any non-No is a valid input (including blanks) and since there are 5 columns just subtract the number we don't want to count.

I'm really not clear one what you are trying to do but maybe this will help you figure it out.

Craig

• Options

Craig you saved my day! I tried the below and it worked.

=IF(OR([Solved]1 = "No", [Protected]1 = "No", [PCI]1 = "No"), 0, (((COUNTIF([A]1:[PCI]1, "Yes") * 1 + (COUNTIF([A]1:[PCI]1, "Not Applicable") * 1))) / 8))

Barb

• ✭✭✭✭✭✭
Options

I do that a lot, mostly at night dressed as a superhero(tm).

Sometimes here too.