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

When you want to count 9 I'm assuming you do not want to count the 9 in 19
or 29 or 39.

=SUMPRODUCT(--(ISNUMBER(SEARCH(" 9"," "&Apr_09!R11:R281))))

--
Biff
Microsoft Excel MVP


"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

Hello Mike,

if you have a set of numbers separted by comma it is a text not number, one
way to do what are you looking for, is separate the ranges using

data | text to columns - Delimited by comma and format as general.


so ther formula could be =countif(a1:e999,9)

hth
--
regards from Brazil
Thanks in advance for your feedback
Marcelo



"watermt" escreveu:

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

=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

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

T. Valko,
Fantastic, this is exactly what I needed, thanks. By the way we copying
formulas to other cells, is there an easy way to do this so the formula
recognizes the correct cell number each time it's copied and pasted into a
new cell?

Mike

"T. Valko" wrote:

When you want to count 9 I'm assuming you do not want to count the 9 in 19
or 29 or 39.

=SUMPRODUCT(--(ISNUMBER(SEARCH(" 9"," "&Apr_09!R11:R281))))

--
Biff
Microsoft Excel MVP


"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

Assuming you mean that you want the referenced range to not change:

=SUMPRODUCT(--(ISNUMBER(SEARCH(" 9"," "&Apr_09!$R$11:$R$281))))

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
T. Valko,
Fantastic, this is exactly what I needed, thanks. By the way we copying
formulas to other cells, is there an easy way to do this so the formula
recognizes the correct cell number each time it's copied and pasted into a
new cell?

Mike

"T. Valko" wrote:

When you want to count 9 I'm assuming you do not want to count the 9 in
19
or 29 or 39.

=SUMPRODUCT(--(ISNUMBER(SEARCH(" 9"," "&Apr_09!R11:R281))))

--
Biff
Microsoft Excel MVP


"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

I follow what you mean "if the referenced range does "not" change, but what
if I want the range to change as the imported data range changes?

The range change will most likely increases beyond R281 - with the ranges
starting cell remiaining the same at R11.

Mike

"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

You can either use a dynamic range that automatically adjusts for the amount
of data you have:

http://contextures.com/xlNames01.html#Dynamic

Or, you can just make the referenced range big enough to cover any expected
amount of data. For example, you know that you never exceed 500 rows of data
so use R11:R500 as the range.

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
I follow what you mean "if the referenced range does "not" change, but what
if I want the range to change as the imported data range changes?

The range change will most likely increases beyond R281 - with the ranges
starting cell remiaining the same at R11.

Mike

"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

I'm going with the Dynamic Range function thanks! One more question
regarding the use of this validation list. How can I set this up so staff
can begin typing in the last name to locate an individual rather than having
to scroll through the entire list?

Mike

"T. Valko" wrote:

You can either use a dynamic range that automatically adjusts for the amount
of data you have:

http://contextures.com/xlNames01.html#Dynamic

Or, you can just make the referenced range big enough to cover any expected
amount of data. For example, you know that you never exceed 500 rows of data
so use R11:R500 as the range.

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
I follow what you mean "if the referenced range does "not" change, but what
if I want the range to change as the imported data range changes?

The range change will most likely increases beyond R281 - with the ranges
starting cell remiaining the same at R11.

Mike

"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

If using a Data Validation List Dropdown, there is no Autocomplete function.

Debra Dalgleish shows how to use a Combobox in conjunction with the DV in
order to enable autocomplete.

http://www.contextures.on.ca/xlDataVal10.html

An alternative using a couple more cells comes from Ashish Mather

Please take a look at question 44 of the following link -
http://ashishmathur.com/replies.aspx

Another method......................

When setting up the List make sure your List is sorted alphabetically then
add a single letter at top of each group of items like A, B, C etc.

Then type a letter, say H, in the dropdown..........don't hit ENTER but
click on the arrow.

You will be taken to top of "H" items.

Thanks to Howard Kittle for this.


Gord Dibben MS Excel MVP

On Tue, 19 May 2009 06:46:01 -0700, watermt
wrote:

I'm going with the Dynamic Range function thanks! One more question
regarding the use of this validation list. How can I set this up so staff
can begin typing in the last name to locate an individual rather than having
to scroll through the entire list?

Mike

"T. Valko" wrote:

You can either use a dynamic range that automatically adjusts for the amount
of data you have:

http://contextures.com/xlNames01.html#Dynamic

Or, you can just make the referenced range big enough to cover any expected
amount of data. For example, you know that you never exceed 500 rows of data
so use R11:R500 as the range.

--
Biff
Microsoft Excel MVP


"watermt" wrote in message
...
I follow what you mean "if the referenced range does "not" change, but what
if I want the range to change as the imported data range changes?

The range change will most likely increases beyond R281 - with the ranges
starting cell remiaining the same at R11.

Mike

"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

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

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

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

Try this:

A2:A4 = strings of numbers

E2:En = numbers to be counted

For example:

E2 = 1
E3 = 2
E4 = 3
E5 = 4
etc
etc

Entered in F2 and copied own as needed.

This may be kind of hard to read with all the commas and quotes:

=SUMPRODUCT(LEN(","&A$2:A$4&",")-LEN(SUBSTITUTE(","&A$2:A$4&",",","&E2&",","")))/LEN(","&E2&",")

--
Biff
Microsoft Excel MVP


"jellyroller" wrote in message
...
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

That has done the trick perfectly - thanks!

"T. Valko" wrote:

Try this:

A2:A4 = strings of numbers

E2:En = numbers to be counted

For example:

E2 = 1
E3 = 2
E4 = 3
E5 = 4
etc
etc

Entered in F2 and copied own as needed.

This may be kind of hard to read with all the commas and quotes:

=SUMPRODUCT(LEN(","&A$2:A$4&",")-LEN(SUBSTITUTE(","&A$2:A$4&",",","&E2&",","")))/LEN(","&E2&",")

--
Biff
Microsoft Excel MVP


"jellyroller" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jellyroller" wrote in message
...
That has done the trick perfectly - thanks!

"T. Valko" wrote:

Try this:

A2:A4 = strings of numbers

E2:En = numbers to be counted

For example:

E2 = 1
E3 = 2
E4 = 3
E5 = 4
etc
etc

Entered in F2 and copied own as needed.

This may be kind of hard to read with all the commas and quotes:

=SUMPRODUCT(LEN(","&A$2:A$4&",")-LEN(SUBSTITUTE(","&A$2:A$4&",",","&E2&",","")))/LEN(","&E2&",")

--
Biff
Microsoft Excel MVP


"jellyroller" wrote in message
...
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