You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
Thanks T. Valko, your sample file cleared up all my questions and
mistakes,
my count is completed and accurate.

Have a great weekend!

"T. Valko" wrote:

Here's a small sample file that demonstrates this:

xWater.xls 14kb

http://cjoint.com/?iCsXTgzUHJ

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
Okay, I got the array formula entered, sticky keys on the keyboard -
force
helps sometimes!

But now, I only get a count of the number 1 from the first cell in my
worksheet which is S3. It's not looking for the number 1 in all cells:

&'Aug 09'!S$3:S$178&

"watermt" wrote:

When I press CTRL+SHIFT, then hit ENTER the formula appears in the
cell?
What might I be doing wrong?

"T. Valko" wrote:

Try this:

=SUM(LEN(","&'Aug 09'!A$1:A$4&",")-LEN(SUBSTITUTE(","&'Aug
09'!A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",")

Don't forget, array entered!!!


--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
This looks like it will work but I inadvertently omitted one piece
of
information which is crucial. The numbers which are being counted
are on
a
wroksheet named Aug 09, but the result of the SUM foemula
worksheet
is
titled
DietIssuesCodes. Where in the formula would I insert each
worksheets
name?

Mike

"T. Valko" wrote:

Let's assume you have this data:

A1 = 1,5,10,11,21
A2 = 12,5,31,32,1
A3 = 1,7,10,21,39
A4 = 1,2,3,1,11

Number delimited by commas.

To count how many instances of the number 1...

C1 = 1

Array entered** :

=SUM(LEN(","&A$1:A$4&",")-LEN(SUBSTITUTE(","&A$1:A$4&",",","&C1&",","")))/LEN(","&C1&",")

Result = 5

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key
and
the
SHIFT
key then hit ENTER.

That's very cryptic and may induce headaches if you stare at it
too
long!
<g

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
I need to come up with a formula (e.g., COUNTIF, VLOOKUP) that
will
look
at
each cell in a column and count the number of occurence for
each
number
in
all cells in the column.

I'm not sure which formula to use or which 'seperator' (i.e.,
comma,
dash,
etc.) to use within the cell. My numbers are inclusive of 1
through
43,
and
I do not want to count the number 1 as number 1 when it appears
as
10,
11,
12, 21, 31, 41 and so on.

I thank you in advance for any and all assistance,
Mike