counting instances

  • Thread starter Thread starter Rob582
  • Start date Start date
R

Rob582

Hi, is it possible to count the whole worksheet by how
many instances of a all numbers occur?
 
To explain a little more, i have the sheet filled with
numbers, with many duplicates, and I wish to count how
many times all the numbers on the sheet occur.
 
Go to the big list of numbers, make sure it has a heading above them in one
cell. Then select that area, go to Data>Filter>Advanced Filter and click
unique entries. Then copy and paste the unique numbers, and then go to
Data>Filter and click SHOW ALL to unfilter the list.

Then, next to the list of unique numbers you have just pasted (above the
other numbers if possible), you can just use countif to tally how many times
each number appears.

E.g. your list A10:A13
1
2
1

You filter this range and paste unique values in another area say A1 and
down, and get
1
2

To the side of these numbers, use something like
countif($a$10:$a$13,a1)

be sure you use the $ around the row numbers in the formula so they will
remain as you copy the formula down (in this example you copy it down twice
since your unique entries list has 2 rows).
 
Back
Top