counting records

  • Thread starter Thread starter fuchka
  • Start date Start date
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)
 
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)
 
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
 
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
 
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.
 
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
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicate Records 4
How do I reset autonumbering without affecting previous records? 1
multiple records on the same form 1
Explained: Re-shipment scams (work at home) and what they do when theyhave your credit-card info 0
FWT Newsletter - Weekly - October 4, 2004 2
"FWT Newsletter - Weekly - August 30, 2004 6
FWT Newsletter - Weekly - August 23, 2004 6

Back
Top