Multi-conditions with SUMPRODUCT and COUNTIF

[Guest]

I'm still struggling a bit with putting a lot of things in the same function,
so I'm looking for a little help on the following problem:

Column B contains a lab result value; Column D contains a Pass/Fail value.
I'm trying to calculate a pass/fail percentage for specific ranges of the lab
result (i.e. APC<=500, 500<APC<=1000). The way I want the formula to run is

(APC<=500 and NOT(ISNA(PassFail)) and PassFail=1)/(PassFail=1 +
PassFail=0)

If that makes sense, please help me out. If not, let me know and I'll try
and put down more concisely what I want.

 

[Biff]

Hi!

If that makes sense, please help me out. If not, let me know and I'll try
and put down more concisely what I want.

Another explanation won't hurt!

(APC<=500 and NOT(ISNA(PassFail)) and PassFail=1)/(PassFail=1 + PassFail=0)

What's this part all about: NOT(ISNA(PassFail))

Biff

 

[Guest]

sounds like this for data in rows 1:100

=sumproduct((b1:b100<=500)*(d1:d100=1))/
sumproduct((isnumber(d1:d100))*1)

to give a ratio of the occurances of column d =1 with column b <=500 to the
total occurance of a number being in column d