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

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

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

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

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

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

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

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

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

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

"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 :-).

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