Formula Help IF AND (ORs) and Counts
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
Comments
-
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
-
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.
-
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
-
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives