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

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

--
Regards,
Dave


"TMF in MN" wrote:

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

Hi

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

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

hth
regards from Brazil
Marcelo



"TMF in MN" escreveu:

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

Try

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

etc.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=571528