Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count individual digits
Sorry about the blank post.
I am trying to come up with a way to count individual digits i a list. I have 2 list in column b & c that go from row 1 - 38. I need to know how to count the total number of times each digit, 0,1,2,3,etc, appears in the list. Is there a function that will do this? Thank you, -- James Bonds Excel Learner |
#2
|
|||
|
|||
Hi James
the COUNTIF function should give you this =COUNTIF(B1:C38,0) or if 0 is in cell E1 =COUNTIF($B$1:$C$38,E1) and fill down for the other values -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... Sorry about the blank post. I am trying to come up with a way to count individual digits i a list. I have 2 list in column b & c that go from row 1 - 38. I need to know how to count the total number of times each digit, 0,1,2,3,etc, appears in the list. Is there a function that will do this? Thank you, -- James Bonds Excel Learner |
#3
|
|||
|
|||
I have tried COUNTIF and it did not work. Let me explain what I meant by
individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5 digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2, 3, etc. show up within all of the cells. Sorry about not clarifying that in the begining. Thank you. -- James Bonds Excel Learner "JulieD" wrote: Hi James the COUNTIF function should give you this =COUNTIF(B1:C38,0) or if 0 is in cell E1 =COUNTIF($B$1:$C$38,E1) and fill down for the other values -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... Sorry about the blank post. I am trying to come up with a way to count individual digits i a list. I have 2 list in column b & c that go from row 1 - 38. I need to know how to count the total number of times each digit, 0,1,2,3,etc, appears in the list. Is there a function that will do this? Thank you, -- James Bonds Excel Learner |
#4
|
|||
|
|||
Hi James
try =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,"")))) where 0 is the number you're looking for. this will return 5 for the following ........B.............C 1....100...........30 2......5.............500 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... I have tried COUNTIF and it did not work. Let me explain what I meant by individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5 digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2, 3, etc. show up within all of the cells. Sorry about not clarifying that in the begining. Thank you. -- James Bonds Excel Learner "JulieD" wrote: Hi James the COUNTIF function should give you this =COUNTIF(B1:C38,0) or if 0 is in cell E1 =COUNTIF($B$1:$C$38,E1) and fill down for the other values -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... Sorry about the blank post. I am trying to come up with a way to count individual digits i a list. I have 2 list in column b & c that go from row 1 - 38. I need to know how to count the total number of times each digit, 0,1,2,3,etc, appears in the list. Is there a function that will do this? Thank you, -- James Bonds Excel Learner |
#5
|
|||
|
|||
That did work great!
Thank you! -- James Bonds Excel Learner "JulieD" wrote: Hi James try =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,"")))) where 0 is the number you're looking for. this will return 5 for the following ........B.............C 1....100...........30 2......5.............500 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... I have tried COUNTIF and it did not work. Let me explain what I meant by individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5 digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2, 3, etc. show up within all of the cells. Sorry about not clarifying that in the begining. Thank you. -- James Bonds Excel Learner "JulieD" wrote: Hi James the COUNTIF function should give you this =COUNTIF(B1:C38,0) or if 0 is in cell E1 =COUNTIF($B$1:$C$38,E1) and fill down for the other values -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... Sorry about the blank post. I am trying to come up with a way to count individual digits i a list. I have 2 list in column b & c that go from row 1 - 38. I need to know how to count the total number of times each digit, 0,1,2,3,etc, appears in the list. Is there a function that will do this? Thank you, -- James Bonds Excel Learner |
#6
|
|||
|
|||
If each digits appears only once per cell then
=SUMPRODUCT(--(ISNUMBER(FIND(2,A1:A100)))) -- HTH RP (remove nothere from the email address if mailing direct) "James" wrote in message ... I have tried COUNTIF and it did not work. Let me explain what I meant by individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5 digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2, 3, etc. show up within all of the cells. Sorry about not clarifying that in the begining. Thank you. -- James Bonds Excel Learner "JulieD" wrote: Hi James the COUNTIF function should give you this =COUNTIF(B1:C38,0) or if 0 is in cell E1 =COUNTIF($B$1:$C$38,E1) and fill down for the other values -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... Sorry about the blank post. I am trying to come up with a way to count individual digits i a list. I have 2 list in column b & c that go from row 1 - 38. I need to know how to count the total number of times each digit, 0,1,2,3,etc, appears in the list. Is there a function that will do this? Thank you, -- James Bonds Excel Learner |
#7
|
|||
|
|||
forgot to ad that if there could be more than one instance of a number in a
cell, use =SUMPRODUCT(--(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,2,"")))) -- HTH RP (remove nothere from the email address if mailing direct) "James" wrote in message ... I have tried COUNTIF and it did not work. Let me explain what I meant by individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5 digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2, 3, etc. show up within all of the cells. Sorry about not clarifying that in the begining. Thank you. -- James Bonds Excel Learner "JulieD" wrote: Hi James the COUNTIF function should give you this =COUNTIF(B1:C38,0) or if 0 is in cell E1 =COUNTIF($B$1:$C$38,E1) and fill down for the other values -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... Sorry about the blank post. I am trying to come up with a way to count individual digits i a list. I have 2 list in column b & c that go from row 1 - 38. I need to know how to count the total number of times each digit, 0,1,2,3,etc, appears in the list. Is there a function that will do this? Thank you, -- James Bonds Excel Learner |
#8
|
|||
|
|||
you're welcome
-- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... That did work great! Thank you! -- James Bonds Excel Learner "JulieD" wrote: Hi James try =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,"")))) where 0 is the number you're looking for. this will return 5 for the following ........B.............C 1....100...........30 2......5.............500 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... I have tried COUNTIF and it did not work. Let me explain what I meant by individual digits. The numbers in the cells B1:C38 are either 3, 4, or 5 digits, ie. 125, 8047, 19025. I need to know how many total times 0, 1, 2, 3, etc. show up within all of the cells. Sorry about not clarifying that in the begining. Thank you. -- James Bonds Excel Learner "JulieD" wrote: Hi James the COUNTIF function should give you this =COUNTIF(B1:C38,0) or if 0 is in cell E1 =COUNTIF($B$1:$C$38,E1) and fill down for the other values -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "James" wrote in message ... Sorry about the blank post. I am trying to come up with a way to count individual digits i a list. I have 2 list in column b & c that go from row 1 - 38. I need to know how to count the total number of times each digit, 0,1,2,3,etc, appears in the list. Is there a function that will do this? Thank you, -- James Bonds Excel Learner |
#9
|
|||
|
|||
JulieD wrote:
Hi James try =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,"")))) where 0 is the number you're looking for. this will return 5 for the following .......B.............C 1....100...........30 2......5.............500 ============= What's the "--" do Julie? I tried to look it up in the Excel Help system but it won't fess up that such a function exists. Nor do I stumble across anything about it in the "Excel 97 Bible" as an operator or as a function. And it's not described by Excel under the SUMPRODUCT function. Bill |
#10
|
|||
|
|||
Hi Bill
for details on the SUMPRODUCT function itself check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html and here's a link to a newsgroup post by Bob Phillips where he explains the double unary (--) and quotes a post by Harlan Grove on the subject http://tinyurl.com/bv42x (good luck!) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bill Martin -- (Remove NOSPAM from address)" wrote in message ... JulieD wrote: Hi James try =SUMPRODUCT(--(LEN(B1:C38)-LEN(SUBSTITUTE(B1:C38,0,"")))) where 0 is the number you're looking for. this will return 5 for the following .......B.............C 1....100...........30 2......5.............500 ============= What's the "--" do Julie? I tried to look it up in the Excel Help system but it won't fess up that such a function exists. Nor do I stumble across anything about it in the "Excel 97 Bible" as an operator or as a function. And it's not described by Excel under the SUMPRODUCT function. Bill |
#11
|
|||
|
|||
"JulieD" wrote in message ... Hi Bill for details on the SUMPRODUCT function itself check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html and here's a link to a newsgroup post by Bob Phillips where he explains the double unary (--) and quotes a post by Harlan Grove on the subject http://tinyurl.com/bv42x But I used it, I didn't sue it :-). |
#12
|
|||
|
|||
Bob Phillips wrote:
"JulieD" wrote in message ... Hi Bill for details on the SUMPRODUCT function itself check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html and here's a link to a newsgroup post by Bob Phillips where he explains the double unary (--) and quotes a post by Harlan Grove on the subject http://tinyurl.com/bv42x But I used it, I didn't sue it :-). ============= Thanks to Julie and Bob both for the education. Somehow I've never stumbled across that usage before. Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count digits within cell | Excel Discussion (Misc queries) | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |