Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multi-conditions with SUMPRODUCT and COUNTIF
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. |
#2
|
|||
|
|||
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 "MikeDH" wrote in message ... 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. |
#3
|
|||
|
|||
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 "MikeDH" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct or CountIf | Excel Worksheet Functions | |||
countif, sumproduct | New Users to Excel | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
Using COUNTIF with 2 criteria - SUMPRODUCT? | Excel Worksheet Functions | |||
SUMPRODUCT & COUNTIF | Excel Worksheet Functions |