countif criteria

[johnT]

can countif criteria be based on 2 or more sets of data??
such as: =COUNTIF(A1:A8,"=AND(a,b)")

thanks......

 

[KL]

Hi John,

For your example (although I think you meant OR not AND) you may do
something like this:

=COUNTIF(A1:A8,"=a")+COUNTIF(A1:A8,"="b")

or

=SUMPRODUCT(--(A1:A8={"a","b"}))

For more complex multiple conditions use SUMPRODUCT(), e.g.

=SUMPRODUCT((A1:A8>0)*(A1:A8<>6)*(A1:A8<100))

Regards,
KL

 

[KL]

sorry - misprint. the first formula should read:

=COUNTIF(A1:A8,"a")+COUNTIF(A1:A8,"b")

KL

 

[Bob Phillips]

Won't work with your other question

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ragdyer]

How about summing your "Countif" examples, which probably answers the
original OP question:

=SUM(COUNTIF(A1:A8,{"a","b"}))

 

[Harlan Grove]

can countif criteria be based on 2 or more sets of data??
such as: =COUNTIF(A1:A8,"=AND(a,b)")

No. If you really mean *AND*, and the 'a' and 'b' would be things the cells
in A1:A8 could equal, then if a <> b, this COUNTIF would always necessarily
equal zero. If a = b, then no point to including b.

On the other hand, if 'a' and 'b' are just tokens for different conditions,
e.g., 'a' = greater than 10, and 'b' = odd integer, then you'd need to use
SUMPRODUCT, as in

=SUMPRODUCT(--(A1:A8>10),--(MOD(A1:A8,2)=1))