Formula Help IF AND (ORs) and Counts

asimcock ✭✭
edited 12/09/19 in Smartsheet Basics


I'm attempting to add a formula to my main sheet to prefill a column based on two other columns inputs.

So if columns ID Pass/Fail AND NPPES Pass/Fail are "Pass" I want "Pass" to prefill in the column this formula will be in.  If there's a mismatch of one Pass and one Fail, or if both are Fails, the row should be filled in "Fail". However, when I have just the both pass and both fail portions in, I get an Unparseable error.


=IF(AND([ID Pass/Fail]1 = "Pass", [NPPES Pass/Fail]1 = "Pass"), "Pass"), OR(IF(AND([ID Pass/Fail]1 = "Fail", [NPPES Pass/Fail]1 = "Fail"), "Fail")))

Could someone please take a look and let me know what they see?


I'm also trying to create a sum sheet for the Dashboard.

On a new sheet I am trying to count 

How many overall pass and overall fails are recorded for each Context ID. Here's the formula for the Count of "Pass" I will have a separate column for Fails in this sheet, so I figured once I had this one built out I can copy it over and change the match term.

=COUNTIFS({October 2019 Audit List Context ID-Overall}, {October 2019 Audit List Context ID} = [Context ID]1, {October 2019 Audit List Overall Pass/Fail} = "Pass")

If someone doesn't mind taking a look please, I'd appreciate any assist!








  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    For the Pass/Fail...


    =IF(OR([ID Pass/Fail]@row= "Fail", [NPPES Pass/Fail]@row= "Fail"), "Fail", "Pass")


    For the count...


    =COUNTIFS({October 2019 Audit List Context ID-Overall}, [Context ID]@row, {October 2019 Audit List Overall Pass/Fail}, "Pass")

  • Hi Paul,


    Thank you so much for taking a look.I had a few follow-up questions if you don't mind.


    For the Pass/Fail, the formula works if a row has both "Pass" or both "Fail".  The hiccup I"m running into is that I need both columns to match in order to have the Overall Status be correct

    ID Pass & NPPES Pass=Overall Status "Pass"

    ID Fail & NPPES Fail=Overall Status "Fail"

    ID Pass & NPPES Fail(or vice versa)-Overall Status this can either be blank or "Incomplete"


    For the count

    I created a new Stat Sheet for the formulas, on the data sheet I have a column with all Context IDs listed once I am using that as the reference for the formula to match with the Context ID in the range of data's Context ID column.

    So when I tried to recreate the formula I'm getting an incorrect Argument Set

    Here's the formula I recreated:

    =COUNTIFS({EPCS October 2019 Audit List Context ID-Overall}, {EPCS October 2019 Audit List Context ID}, [Context ID]1, {EPCS October 2019 Audit List Overall Pass/Fail}, "Pass")

    Here's the source id instead of the column headers:

    =COUNTIFS(Datasheet reference range}, {data sheet Context ID}, [Stat Sheet Context ID]1, {Datasheet Overall Pass/Fail}, "Pass")


    So ultimately, if I had 5 rows for Context ID 1 with three rows having "Pass" and two rows having "Fail" I would want to have the data sheet to display:

    Column 1 prefilled Context ID

    Column 2 Count of Overall "Pass Status" Formula returning value 3

    Column 3 Count of Overall "Fail" Status.Formula returning value 2

    Thanks again for taking a look, I appreciate it!




  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. Ok. I must have misread your original post about the Pass/Fail.

    So to get


    Pass/Pass = Pass

    Fail/Fail = Fail

    All others "Incomplete"


    You would use...


    =IF(AND([ID Pass/Fail]@row= "Fail", [NPPES Pass/Fail]@row= "Fail"), "Fail", IF(AND([ID Pass/Fail]@row= "Pass", [NPPES Pass/Fail]@row= "Pass"), "Pass", "Incomplete"))


    For the counts...

    Here is your formula:

    =COUNTIFS({EPCS October 2019 Audit List Context ID-Overall}, {EPCS October 2019 Audit List Context ID}, [Context ID]1, {EPCS October 2019 Audit List Overall Pass/Fail}, "Pass")


    Here is how it should look:

    =COUNTIFS({EPCS October 2019 Audit List Context ID}, [Context ID]1, {EPCS October 2019 Audit List Overall Pass/Fail}, "Pass")


    You are including an extra range at the beginning of the formula.

    It should be 


    but you are entering



    Remove that first range, and you should be good to go.

  • Hi Paul,

    Thank you so much for your help, both the formulas worked perfectly and I was able to create additional counts based on your advice above.


    Have a great day,