"plb2862" wrote in message
news:BZ0Og.7007$rT5.4966@fed1read01...
"plb2862" wrote in message
news:jT0Og.7006$rT5.5112@fed1read01...
wrote in message
ups.com...
I have the following situation:

10000
11000
12000
13000
11100
11200
11300
12100
12200
12300
20000
21000
22000
23000
21100
21200
21300

What i need is formula to scroll through this records and count:

A) Number of records starting with 1 (10000)
B) Number of record starting with two digits, and first one is 1, and
rest are zero (11000,12000,13000)
C) Number of record starting with two digits, and first one is 2, and
rest are zero (21000,22000,23000)
D) Number of record starting with three digits, and first one is 1, and
rest are zero
(12100, 122000, 123000)
E) Number of record starting with three digits, and first one is 2, and
rest are zero
(21100, 212000, 213000)

Here is the closest I come to what you want. Column A are your numbers,
column B counts the number of 0s in the number
=IF(LEN(TRIM(B2))=0,0,LEN(TRIM(B2))-LEN(SUBSTITUTE(B2,"0",""))) and
column C counts how many numbers have 1 0, 2 0s, 3 0s & 4 0s in the
number:
C1=COUNTIF(C$2:C$18,1)
C2=COUNTIF(C$2:C$18,2)
C3=COUNTIF(C$2:C$18,3)
C4=COUNTIF(C$2:C$18,4)

A B C
1 10000 4 0
2 11000 3 9
3 12000 3 6
4 13000 3 2
5 11100 2
6 11200 2
7 11300 2
8 12100 2
9 12200 2
10 12300 2
11 20000 4
12 21000 3
13 22000 3
14 23000 3
15 21100 2
16 21200 2
17 21300 2

That would be
0 have 1 0
9 have 2 0s
6 have 3 0s
2 have 4 0s


I would use Bob Philips first formula =SUMPRODUCT(--(LEFT(A1:A17,1)="1")) to
do the Number of records starting with 1 (1####)
and ignore my post as I mis read the rest.