how to count cells...

[hal9000]

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]

Hi

As you used AND to bind 2 conditions, you can drop B223:B333 part for 2-nd
condition. Then
=SUMPRODUCT((P1222="AAAA")*(B1:B222="BBBB"))

 

[hal9000]

=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((P1222="A*")*(B1:B222="B*"))
=SUMPRODUCT((P1222="A???")*(B1:B222="B???"))

 

[Arvi Laanemets]

Hi

Why not
=SUMPRODUCT((P1222>="A")*(B1:B222>="B"))
=SUMPRODUCT((P1222>="A")*(P1222<"B")*(B1:B222>="B")*(B1:B222<"C"))

 

[Dave Peterson]

Another option:

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