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

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

 

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

 

[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

 

[T. Valko]

Probably the best bet would be...

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

 

[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. Valko]

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

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

 

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

 

[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

 

[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

 

[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. Valko]

Will there be any duplicate numbers in a cell?

For example:

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

4 is duplicated

 

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

 

[jellyroller]

That has done the trick perfectly - thanks!

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

 

[T. Valko]

You're welcome. Thanks for the feedback!