a count formula

[Guest]

I would like a formula to count how many cells contain a code that begin with
B, then with Z and O.

So B's would be 3 Z's 5 and O's 1.

1 BBL
2 BBL
3 BBL
4 ZZ3
5 ZXX
6 ZZ1
7 ZZ1
8 ZZ6
9 ODA

 

[Guest]

Hi

use
=sumproduct(--(left(a2:a100,1)="B"))
or

=sumproduct(--(left(a2:a100,1)=b4))

hth
regards from Brazil
Marcelo



"TMF in MN" escreveu:

 

[Guest]

=SUMPRODUCT(--(LEFT(A1:A10)="B"))
=SUMPRODUCT(--(LEFT(A1:A10)="Z"))
=SUMPRODUCT(--(LEFT(A1:A10)="O"))

 

[mrice]

Try

=COUNTIF(A:A,"Z*")

etc.