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

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