| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting records
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting records
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) 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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting records
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting records
"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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting records
"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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting records
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)))*(M OD($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)))*(M OD($A$1:$A$17,10000)99)) Does that help? *********** Regards, Ron XL2002, WinXP " wrote: 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) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting records
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting records
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)))*(MO D($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)))*(MO D($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)))*(MO D($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)))*(MO D($A$1:$A$24,10^((LEN($A$1:$A$24)-3)))=0)) Does that help? *********** Regards, Ron XL2002, WinXP " wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting records
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting unique records | Excel Worksheet Functions | |||
| Counting records within a month | New Users to Excel | |||
| Counting unique records with additional criteria | Excel Worksheet Functions | |||
| Deleting specific records | Excel Discussion (Misc queries) | |||
| Counting Unique Records with multiple conditions | Excel Worksheet Functions | |||
Linear Mode
