Home |
Search |
Today's Posts |
#1
|
|||
|
|||
countif - Criterion1 OR [Criterion2 AND Criterion3]
I wish to do the following: =countif(TargetCell or TargetCells, Criterion1 OR [Criterion2 AND Criterion3]) eg: =countif(A1, Criterion1 OR [Criterion2 AND Criterion3]) eg: =countif(A1:A10, Criterion1 OR [Criterion2 AND Criterion3]) How to do? Thanks. -- Additional information: - I'm using Office XP - I'm using Windows XP |
#2
|
|||
|
|||
countif won't work well for this.
try sumproduct() =sumproduct(or(targetcells=Crierion1,and(Targetcel ls=criterion2,targetcells=criterion3))) "0-0 Wai Wai ^-^" wrote: I wish to do the following: =countif(TargetCell or TargetCells, Criterion1 OR [Criterion2 AND Criterion3]) eg: =countif(A1, Criterion1 OR [Criterion2 AND Criterion3]) eg: =countif(A1:A10, Criterion1 OR [Criterion2 AND Criterion3]) How to do? Thanks. -- Additional information: - I'm using Office XP - I'm using Windows XP |
#3
|
|||
|
|||
=SUMPRODUCT(--ISNUMBER(MATCH(TargetRange,CritList,0)))
where CritList refers to a range that houses the relevant criteria. 0-0 Wai Wai ^-^ wrote: I wish to do the following: =countif(TargetCell or TargetCells, Criterion1 OR [Criterion2 AND Criterion3]) eg: =countif(A1, Criterion1 OR [Criterion2 AND Criterion3]) eg: =countif(A1:A10, Criterion1 OR [Criterion2 AND Criterion3]) How to do? Thanks. |
#4
|
|||
|
|||
Aladin Akyurek wrote...
=SUMPRODUCT(--ISNUMBER(MATCH(TargetRange,CritList,0))) where CritList refers to a range that houses the relevant criteria. .... That'd work for a single equality criteria, but that's not what the OP asked. Looks like the OP needs something closer to =SUMPRODUCT(--((range<=Crit1)+((range<=Crit2)*(range<=Crit3)) 0)) where <= is just a placeholder for any of the comparison operators. It'd be more complicated with text criteria including wildcards. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|