Formula Help IF AND (ORs) and Counts

asimcock

Hello!

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!

 

Thanks,

Ashley

 

 

 

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

asimcock

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!

Ashley

 

 

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 

range/criteria/range/criteria

but you are entering

range/range/criteria/range/criteria

.

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

asimcock

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,

Ashley

 

Paul Newcome

Happy to help!