Count with 2 conditions, second one OR

J

j.figueras

I need to count occurrences within the same column of having both "A"
and (either 0 or 5). [Actually, I'm using the zero or 5 because what I
really want to do is count if there is both an "A" and any number, but
the zero or 5 will work within my set of numbers.]

Thank you--I've read many great responses Joann
 
B

Bob Phillips

how can it be A and 0 (or) in the same column. If you mean different columns

=SUMPRODUCT(--(A1:A100="A"),--(ISNUMBER(MATCH(B1:B100,{0,5},0))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
R

Ron Coderre

Do your values start with a letter and end with, or contain, a number?
something like this?:
C2 or A5 or A7...etc ,
or C123 or A050...etc

If YES, maybe something like one of these:

For values in A1:A100

This formula counts cells containing A0 or A5:
B1: =SUMPRODUCT(COUNTIF(A1:A100,{"A0","A5"}))

or

This formula counts cells that begin with A and contain a zero or a 5:
B1: =SUMPRODUCT(COUNTIF(A1:A25,{"A*0*","A*5*"}))
(So it would count values like: Abc5, A120, and A302)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Top