how to count cells...

hal9000 · Dec 8, 2003

how to count cells that contains string AAAA in cell P1 to
P222 AND also contains string BBBB in cell B1 to B333?
something i imagine is:

Arvi Laanemets · Dec 8, 2003

Hi

As you used AND to bind 2 conditions, you can drop B223:B333 part for 2-nd
condition. Then
=SUMPRODUCT((P1

222="AAAA")*(B1:B222="BBBB"))

hal9000 · Dec 8, 2003

=SUMPRODUCT((P1222="AAAA")*(B1:B222="BBBB"))

thank you for the reply.
is it possible to use wildcards?
the asterisk and the question mark does not work; ie,
=SUMPRODUCT((P1

222="A*")*(B1:B222="B*"))
=SUMPRODUCT((P1

222="A???")*(B1:B222="B???"))

Arvi Laanemets · Dec 8, 2003

Hi

Why not
=SUMPRODUCT((P1

222>="A")*(B1:B222>="B"))
=SUMPRODUCT((P1

222>="A")*(P1

222<"B")*(B1:B222>="B")*(B1:B222<"C"))

Dave Peterson · Dec 9, 2003

Another option:

=sumproduct((left(p1

222,1)="A")*(left(b1:b222,1)="B"))