Getting a pass/fail rate from 3 values

[ZeeDoubleYou]

What I am trying to do is make a chart to record "pass" and "fail" on a spreadsheet while having some boxes as "null" for being inapplicable for different spaces. When I create a =COUNTIF(C2:E17,"Pass")/COUNTA(C2:E17) it skews the rate because "null" is not "pass" but if I have them hidden as "pass" cells it will skew the rate as being higher than what it should be. Any insight as to how I can exclude the cells or the word in general? Attached is what I've got with the gray cells being what needs to be excluded.

 

[Fluff]

Hi & welcome to MrExcel.
Maybe

=COUNTIF(C2:E17,"Pass")/COUNTIF(C2:E17,"?*")

 

[ZeeDoubleYou]

Hi & welcome to MrExcel.
Maybe

=COUNTIF(C2:E17,"Pass")/COUNTIF(C2:E17,"?*")

Doing this actually reduced the rate rather than increase it unless "?*" refers to something specific I need to fill in.

 

[Fluff]

In that case can you post some sample data, the expected result & how you calculated that result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ZeeDoubleYou]

Processing Safety Inspections.xlsx
	C	D	E	F	G	H	I
1	Control Room	Presses	Peanut Loadout
2	Null	Pass	Pass				Pass Rate
3	Pass	Pass	Pass				50%
4	Null	Pass
5
6
7	Null
8	Null
9
10
11
12
13	Null	Null
14
15
16
17
Area 19

Cell Formulas
Range	Formula
I3	I3	=COUNTIF(C2:E17,"Pass")/COUNTA(C2:E17)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4,C7:C8,C13:D13	Cell Value	contains "Pass"	text	NO
C2	Cell Value	contains "Pass"	text	NO
C2:E17	Cell Value	contains "Fail"	text	NO
C2:E17	Cell Value	contains "Pass"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:E17	List	Pass, Fail, Null

 

[Fluff]

And what is your expected result?

 

[ZeeDoubleYou]

And what is your expected result?

The result I'd like is for the pass rate to be reflected accurately. As the example shows it should reflect 100% pass rate because there have been no fails entered at that time.

 

[Fluff]

Ok, how about

=COUNTIF(C2:E17,"Pass")/COUNTIFS(C2:E17,"<>null",C2:E17,"<>")

 

[ZeeDoubleYou]

Ok, how about

=COUNTIF(C2:E17,"Pass")/COUNTIFS(C2:E17,"<>null",C2:E17,"<>")

That does the trick, thank you.

 

[Fluff]

You're welcome & thanks for the feedback.