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

Find more posts tagged with

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
I have a Pro plan but I cannot create ANY reports
I have a Pro plan but I cannot create ANY reports. The system does not allow me to choose a folder, and consequently a sheet to create a row report and suggests an upgrade to Business or Enterprise. I'm quite new to Smartsheet and feel a bit disappointed with this limitation, or maybe I'm just doing something wrong. Alberto