countif criteria
 

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

thanks......

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:A80)*(A1:A8<6)*(A1:A8<100))

Regards,
KL


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

thanks......

sorry - misprint. the first formula should read:

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

KL


"KL" wrote in message
...
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:A80)*(A1:A8<6)*(A1:A8<100))

Regards,
KL


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

thanks......

Won't work with your other question :-)

--

HTH

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


"KL" wrote in message
...
sorry - misprint. the first formula should read:

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

KL


"KL" wrote in message
...
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:A80)*(A1:A8<6)*(A1:A8<100))

Regards,
KL


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

thanks......

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

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KL" wrote in message
...
sorry - misprint. the first formula should read:

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

KL


"KL" wrote in message
...
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:A80)*(A1:A8<6)*(A1:A8<100))

Regards,
KL


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

thanks......

"johnT" wrote...
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:A810),--(MOD(A1:A8,2)=1))