Count individual digits

[Guest]

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,

 

[JulieD]

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

 

[Guest]

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

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

 

[JulieD]

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

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.

 

[Guest]

That did work great!
Thank you!

 

[Bob Phillips]

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)

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.

 

[Bob Phillips]

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)

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.

 

[JulieD]

you're welcome

 

[Guest]

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]

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

 

[Bob Phillips]

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 .

 

[Guest]

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