counting digits, not numbers
 

Is there a worksheet function that will return the number of times a
digit (eg. The number 15 has a digit 1 and a digit 5) appears in a list
or array of cells.:
For example:
Cell A1 = 15
Cell A2 = 12
Cell A3 = 114
Cell A4 = 43
Cell A5 = 54
When using the array A1:A5, I would like the formula to return the fact
that digit 1 appears 4 times, the digit 2 appears 1 time, digit 3
appears 1 time, digit 4 appears 3 times and digit 5 appears 2 times.


--
nebb
------------------------------------------------------------------------
nebb's Profile: http://www.excelforum.com/member.php...fo&userid=8981
View this thread: http://www.excelforum.com/showthread...hreadid=504230

counting digits, not numbers
 

This will count the 1s

=SUMPRODUCT(--(LEN($A$1:$A$5)-LEN(SUBSTITUTE($A$1:$A$5,"1",""))))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"nebb" wrote in message
...

Is there a worksheet function that will return the number of times a
digit (eg. The number 15 has a digit 1 and a digit 5) appears in a list
or array of cells.:
For example:
Cell A1 = 15
Cell A2 = 12
Cell A3 = 114
Cell A4 = 43
Cell A5 = 54
When using the array A1:A5, I would like the formula to return the fact
that digit 1 appears 4 times, the digit 2 appears 1 time, digit 3
appears 1 time, digit 4 appears 3 times and digit 5 appears 2 times.


--
nebb
------------------------------------------------------------------------
nebb's Profile:
http://www.excelforum.com/member.php...fo&userid=8981
View this thread: http://www.excelforum.com/showthread...hreadid=504230

counting digits, not numbers
 

With your list in cells A1:A5

B1: 1
C1:
=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(UPPER(A1:A5),UPPER(B1),"")))/LEN(B1)

That formula counts the occurrences of the B1 value in the range A1:A5.
In this case, there are 4 ones in that range.

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=504230

counting digits, not numbers
 

UPPER is superfluous here, UPPER(1) is the same as LOWER(1) <vbg

It doesn't help with text either, substitute is not case sensitive

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Ron Coderre"
wrote in message
...

With your list in cells A1:A5

B1: 1
C1:
=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(UPPER(A1:A5),UPPER(B1),"")))/LEN(B1)

That formula counts the occurrences of the B1 value in the range A1:A5.
In this case, there are 4 ones in that range.

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile:
http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=504230

counting digits, not numbers
 

Yeah...I know.
Actually, I posted that on the "other" forum...immediately realized my
mistake...and quickly deleted the entire post (which that forum allows).
BUT, not before the darn thing interfaced to this forum in that 10 second
interval. :\

***********
Regards,
Ron

XL2002, WinXP-Pro


"Bob Phillips" wrote:

UPPER is superfluous here, UPPER(1) is the same as LOWER(1) <vbg

It doesn't help with text either, substitute is not case sensitive

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Ron Coderre"
wrote in message
...

With your list in cells A1:A5

B1: 1
C1:
=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(UPPER(A1:A5),UPPER(B1),"")))/LEN(B1)

That formula counts the occurrences of the B1 value in the range A1:A5.
In this case, there are 4 ones in that range.

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile:
http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=504230