counting records

F

fuchka

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)
 
B

Bob Phillips

A =SUMPRODUCT(--(LEFT(A1:A17,1)="1"))
B
=SUMPRODUCT(--(LEFT(A1:A17,1)="1"),--(MID(A1:A17,2,1)<>"0"),--(RIGHT(A1:A17,
LEN(A1:A17)-2)=REPT("0",LEN(A1:A17)-2)))
C
=SUMPRODUCT(--(LEFT(A1:A17,1)="2"),--(MID(A1:A17,2,1)<>"0"),--(RIGHT(A1:A17,
LEN(A1:A17)-2)=REPT("0",LEN(A1:A17)-2)))
D
=SUMPRODUCT(--(LEFT(A1:A17,1)="1"),--(MID(A1:A17,2,1)<>"0"),--(MID(A1:A17,3,
1)<>"0"),--(RIGHT(A1:A17,LEN(A1:A17)-3)=REPT("0",LEN(A1:A17)-3)))
E
=SUMPRODUCT(--(LEFT(A1:A17,1)="2"),--(MID(A1:A17,2,1)<>"0"),--(MID(A1:A17,3,
1)<>"0"),--(RIGHT(A1:A17,LEN(A1:A17)-3)=REPT("0",LEN(A1:A17)-3)))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
P

plb2862

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)
Click to expand...
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
 
P

plb2862

plb2862 said:
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
Click to expand...
That would be
0 have 1 0
9 have 2 0s
6 have 3 0s
2 have 4 0s
 
P

plb2862

plb2862 said:
That would be
0 have 1 0
9 have 2 0s
6 have 3 0s
2 have 4 0s
Click to expand...
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.
 
R

Ron Coderre

If all of the values will be between 10,000 and 99,999 (inclusive),
maybe these methods?:

a) =SUMPRODUCT(--(INT($A$1:$A$17/10^4)=1))

b)
=SUMPRODUCT(--(INT($A$1:$A$17/10^4)=1)*(MOD($A$1:$A$17,1000)=0)*(MOD($A$1:$A$17,10000)>999))

c)
=SUMPRODUCT(--(INT($A$1:$A$17/10^4)=2)*(MOD($A$1:$A$17,1000)=0)*(MOD($A$1:$A$17,10000)>999))

d)
=SUMPRODUCT(--(INT($A$1:$A$17/10^4)=1)*ISERROR(FIND("0",MID($A$1:$A$17,2,2)))*(MOD($A$1:$A$17,10000)>99))

e)
=SUMPRODUCT(--(INT($A$1:$A$17/10^4)=2)*ISERROR(FIND("0",MID($A$1:$A$17,2,2)))*(MOD($A$1:$A$17,10000)>99))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
R

Ron Coderre

OK.....Per your sample file, the raw numbers are not padded with zeros and
can be of any length.

So, see if these work for you.
a)
=SUMPRODUCT(--(INT($A$1:$A$24*10^(-(LEN($A$1:$A$24)-1)))=1)*(MOD($A$1:$A$24,10^(LEN($A$1:$A$24)-1))=0))
b)
=SUMPRODUCT(--(INT($A$1:$A$24*10^(-(LEN($A$1:$A$24)-1)))=1)*ISERROR(FIND("0",MID($A$1:$A$24,2,1)))*(MOD($A$1:$A$24,10^((LEN($A$1:$A$24)-2)))=0))
c)
=SUMPRODUCT(--(INT($A$1:$A$24*10^(-(LEN($A$1:$A$24)-1)))=2)*ISERROR(FIND("0",MID($A$1:$A$24,2,1)))*(MOD($A$1:$A$24,10^((LEN($A$1:$A$24)-2)))=0))
d)
=SUMPRODUCT(--(INT($A$1:$A$24*10^(-(LEN($A$1:$A$24)-1)))=1)*ISERROR(FIND("0",MID($A$1:$A$24,2,1)))*(MOD($A$1:$A$24,10^((LEN($A$1:$A$24)-3)))=0))
e)
=SUMPRODUCT(--(INT($A$1:$A$24*10^(-(LEN($A$1:$A$24)-1)))=2)*ISERROR(FIND("0",MID($A$1:$A$24,2,1)))*(MOD($A$1:$A$24,10^((LEN($A$1:$A$24)-3)))=0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
P

plb2862

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
Click to expand...
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
 
Top