COUNT function - # of occurences a number appears in all cells

W

watermt

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

T. Valko

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))))
 
T

T. Valko

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))))
 
M

Marcelo

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:
 
M

Marcelo

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:
 
B

Bob Umlas

=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
 
B

Bob Umlas

=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
 
W

watermt

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
 
W

watermt

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

T. Valko

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

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

T. Valko

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

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

watermt

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
 
W

watermt

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

T. Valko

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.
 
T

T. Valko

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.
 
W

watermt

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
 
W

watermt

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
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count function 8
conditional count of single occurences 4
Count with merged cells 2
count cells in a document, but excude cells with a "0" 1
Excel Need Countifs Formula Help 0
count specific text that occurs in a range of cells 16
Excel Use COUNTIF across a range of cells with formulas 0
max limit to characters in COUNTIF function? 2

Top