Count individual digits

J

James

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,
 
J

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
 
J

James

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 said:
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
Click to expand...
 
J

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
James said:
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.
Click to expand...
 
B

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)


James said:
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.
Click to expand...
 
B

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)


James said:
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.
Click to expand...
 
G

Guest

JulieD said:
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
Click to expand...

=============

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
 
G

Guest

Bob said:
But I used it, I didn't sue it :).
Click to expand...
=============

Thanks to Julie and Bob both for the education. Somehow I've never
stumbled across that usage before.

Bill
 
Top