Excel 2010 Forumula help

collinseliza

hi there,
I have a huge selection of numbers (2000+) that i need to rank in order of popularity/ how often they appear on the excel sheet. I have tried the countif function but its going to take to long to set the formula for each unique number. Is there another way of being able to capture this information so that I can get a breakdown of how often a number appears.

Ultimately i want to be able to put it on a chart to display the breakdown and then to list the top 10.

Many thanks

sebphoto

collinseliza wrote: »

hi there,
I have a huge selection of numbers (2000+) that i need to rank in order of popularity/ how often they appear on the excel sheet. I have tried the countif function but its going to take to long to set the formula for each unique number. Is there another way of being able to capture this information so that I can get a breakdown of how often a number appears.

Ultimately i want to be able to put it on a chart to display the breakdown and then to list the top 10.

Many thanks

I would use countif
countif(range;value_or_text_to_search)

countif($A$1:$A$3000;A1)
countif($A$1:$A$3000;A2)
countif($A$1:$A$3000;A3)
.. copy & paste

This shouldn't take long time.

Overheal

Phantom99

Hi,

Create a pivot table with amount in the rows and count of instances of that number in the values box.

superb choice of username

Pivottable is definitely the quickest and most efficient way to go.

Phantom99

superb choice of username wrote: »

Pivottable is definitely the quickest and most efficient way to go.

I find it more reliable too