counting digits, not numbers

[nebb]

Is there a worksheet function that will return the number of times
digit (eg. The number 15 has a digit 1 and a digit 5) appears in a lis
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 fac
that digit 1 appears 4 times, the digit 2 appears 1 time, digit
appears 1 time, digit 4 appears 3 times and digit 5 appears 2 times

 

[Bob Phillips]

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)

 

[Ron Coderre]

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

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

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

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" <[email protected]>
wrote in message