wrote in message
ups.com...
so it kinda didn't work ..
please check my excel file to see what i am trying to do ... i would be
very thankfull for any help

http://www.fuchka.info/tmp/example.xls

One of the problems is that you are using a format in stead of a number
something like custom 0######### on some numbers and 00######## on others.
So there is a problem in that when using LEN, LEFT, RIGHT etc... they work
on actual characters not format. Then I would use Bob Philips functions
which are perfect. This is the result I get when I use numbers instead of
format.

7 Numbers that start with a 1
3 Number of record starting with two digits, and first one is 1, and rest
are zero
3 Number of record starting with two digits, and first one is 2, and rest
are zero
1 Number of record starting with three digits, and first one is 1, and rest
are zero
1 Number of record starting with three digits, and first one is 2, and rest
are zero
1 Number of record starting with three digits, and first one is 3, and rest
are zero
1 Number of record starting with three digits, and first one is 4, and rest
are zero

=SUMPRODUCT(--(LEFT(O1:O24,1)="1"))
=SUMPRODUCT(--(LEFT(O1:O24,1)="1"),--(MID(O1:O24,2,1)<"0"),--(RIGHT(O1:O24,LEN(O1:O24)-2)=REPT("0",LEN(O1:O24)-2)))
=SUMPRODUCT(--(LEFT(O1:O17,1)="2"),--(MID(O1:O17,2,1)<"0"),--(RIGHT(O1:O17,LEN(O1:O17)-2)=REPT("0",LEN(O1:O17)-2)))
=SUMPRODUCT(--(LEFT(O1:O24,1)="1"),--(MID(O1:O24,2,1)<"0"),--(MID(O1:O24,3,1)<"0"),--(RIGHT(O1:O24,LEN(O1:O24)-3)=REPT("0",LEN(O1:O24)-3)))
=SUMPRODUCT(--(LEFT(O1:O24,1)="2"),--(MID(O1:O24,2,1)<"0"),--(MID(O1:O24,3,1)<"0"),--(RIGHT(O1:O24,LEN(O1:O24)-3)=REPT("0",LEN(O1:O24)-3)))
=SUMPRODUCT(--(LEFT(O1:O24,1)="3"),--(MID(O1:O24,2,1)<"0"),--(MID(O1:O24,3,1)<"0"),--(RIGHT(O1:O24,LEN(O1:O24)-3)=REPT("0",LEN(O1:O24)-3)))
=SUMPRODUCT(--(LEFT(O1:O24,1)="4"),--(MID(O1:O24,2,1)<"0"),--(MID(O1:O24,3,1)<"0"),--(RIGHT(O1:O24,LEN(O1:O24)-3)=REPT("0",LEN(O1:O24)-3)))

And you can modify his formulas to go on and on:

Number of record starting with four digits, and first one is 1, and rest
are zero
Number of record starting with four digits, and first one is 2, and rest
are zero
Number of record starting with four digits, and first one is 3, and rest
are zero
Number of record starting with four digits, and first one is 4, and rest
are zero