countif criteria

J

johnT

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

thanks......
 
K

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
 
K

KL

sorry - misprint. the first formula should read:

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

KL
 
B

Bob Phillips

Won't work with your other question :)

--

HTH

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

Ragdyer

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

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

Harlan Grove

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

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))
 
Top