software

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: www.excelforum.com/member.php...foamp;userid=8981
View this thread: www.excelforum.com/showthread...hreadid=504230This will count the 1s

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;nebbquot; gt; wrote in message
...
gt;
gt; Is there a worksheet function that will return the number of times a
gt; digit (eg. The number 15 has a digit 1 and a digit 5) appears in a list
gt; or array of cells.:
gt; For example:
gt; Cell A1 = 15
gt; Cell A2 = 12
gt; Cell A3 = 114
gt; Cell A4 = 43
gt; Cell A5 = 54
gt; When using the array A1:A5, I would like the formula to return the fact
gt; that digit 1 appears 4 times, the digit 2 appears 1 time, digit 3
gt; appears 1 time, digit 4 appears 3 times and digit 5 appears 2 times.
gt;
gt;
gt; --
gt; nebb
gt; ------------------------------------------------------------------------
gt; nebb's Profile:
www.excelforum.com/member.php...foamp;userid=8981
gt; View this thread: www.excelforum.com/showthread...hreadid=504230
gt;

With your list in cells A1:A5

B1: 1
C1:
=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(UPPER(A1:A5),UPPER(B1),quot;quot;)))/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: www.excelforum.com/member.php...oamp;userid=21419
View this thread: www.excelforum.com/showthread...hreadid=504230UPPER is superfluous here, UPPER(1) is the same as LOWER(1) lt;vbggt;

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Ron Coderrequot; gt;
wrote in message
...
gt;
gt; With your list in cells A1:A5
gt;
gt; B1: 1
gt; C1:
gt; =SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(UPPER(A1:A5),UPPER(B1),quot;quot;)))/LEN(B1)
gt;
gt; That formula counts the occurrences of the B1 value in the range A1:A5.
gt; In this case, there are 4 ones in that range.
gt;
gt; Does that help?
gt;
gt; Regards,
gt; Ron
gt;
gt;
gt; --
gt; Ron Coderre
gt; ------------------------------------------------------------------------
gt; Ron Coderre's Profile:
www.excelforum.com/member.php...oamp;userid=21419
gt; View this thread: www.excelforum.com/showthread...hreadid=504230
gt;
Yeah...I know.
Actually, I posted that on the quot;otherquot; 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-Proquot;Bob Phillipsquot; wrote:

gt; UPPER is superfluous here, UPPER(1) is the same as LOWER(1) lt;vbggt;
gt;
gt; It doesn't help with text either, substitute is not case sensitive
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Ron Coderrequot; gt;
gt; wrote in message
gt; ...
gt; gt;
gt; gt; With your list in cells A1:A5
gt; gt;
gt; gt; B1: 1
gt; gt; C1:
gt; gt; =SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(UPPER(A1:A5),UPPER(B1),quot;quot;)))/LEN(B1)
gt; gt;
gt; gt; That formula counts the occurrences of the B1 value in the range A1:A5.
gt; gt; In this case, there are 4 ones in that range.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Ron Coderre
gt; gt; ------------------------------------------------------------------------
gt; gt; Ron Coderre's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21419
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=504230
gt; gt;
gt;
gt;
gt;