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))))
 
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
 
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))))
 
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.
 
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
 
J

jellyroller

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

T. Valko

Will there be any duplicate numbers in a cell?

For example:

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

4 is duplicated
 
J

jellyroller

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

T. Valko

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&",")
 
J

jellyroller

That has done the trick perfectly - thanks!

T. Valko said:
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&",")
 

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

Top