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