I am hoping not but the purpose of this formula will be to highlight if there
are (i.e. if the count is greater than 1 (in one cell or all cells) it will
return a message telling the user to correct it)

"T. Valko" wrote:

Will there be any duplicate numbers in a cell?

For example:

A2: 3,4,4,4,7,10

4 is duplicated

--
Biff
Microsoft Excel MVP


"jellyroller" wrote in message
...
Hi,

I have a similar problem to OP - I have a series of data in cells
seperated
by a comma and no spaces (e.g. A2="3,5,7,42,59,60,61,62,63,64",
A3="2,4",A4="6" etc) and I want to count all the individual occurences of
each number, so the count of 6 should just be 1 in this example. I have
tried
using your formula but I cant quite work out how to get it working for
number
1-9 - what do I need to change?

Thanks in advance and sorry if its really straightforward!

Ed

"T. Valko" wrote:

Probably the best bet would be...

....to see Biff's reply! <grin

--
Biff
Microsoft Excel MVP


"Bob Umlas" wrote in message
...
=SUMPRODUCT(N(NOT(ISERROR(FIND(9,R11:R281)))))
will work, but it will also count 9's found in 19, 29,793, etc.
You might toy with the formula and search for "9," but that would found
29,
or you might try " 9," but that would not find a leading 9 or just a 9.
So clearly, it's not trivial.
Probably the best bet would be a user-defined function.

Bob Umlas

"watermt" wrote in message
...
Using Excel 2003:
I'm trying to use the =COUNTIF function to count the number of
occurrences a
number appears (numbers 1 through 43) in cells within a column. Each
cell
can contain only one number or multiple numbers with no duplicates per
cell.

Here the formula:
=COUNTIF(Apr_09!R11:R281,"=9")

eg. cell R22 contains 2, 9, 34, 22, 10; cell R14 contains 1, 9, 33,
20,
15;
cell R30 contains 1, 9, 16, 27

I need a COUNT formula similar as above to look at the entire Range
(R11:R281) and count the number of time the number 9 appears in a
cell.
There can never be more than one instance of a number in each cell
(i.e.,
number 9 cannot appear in cell R30 more than once).

Any help is greatly appreciated,
Mike