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

edited 12/09/19 in Archived 2016 Posts


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,



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    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.


    Let's start with the first part, this 


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




  • Barb

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


    Appreciate your quick response!


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

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

    Sometimes here too. Laughing

    Glad I can help.



This discussion has been closed.