If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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. |
Thread Tools | |
Display Modes | |
|
|