MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Frequency of Values

Posted by Peter on December 19, 2000 1:27 PM

I have a large worksheet that I use to analyse various pieces of data. In one of the columns I have a piece of data that I wish to establish how many time it repeats itself.
I could sort the data and do it that way, but I would prefer to be able to do it via a formula.

Does anyone have any ideas>


Posted by Bruce on December 19, 2000 3:44 PM

An easy way is to use a pivot table, (data, pivot table) select just all the cells in the column, then drag the column name into the row, and then drag the column name into the data. If the column name in data does not same "count of column name", double click on it and change to count.
This will give you a list of an items in the column, and a count of each item.

Posted by Celia on December 19, 2000 5:03 PM

Alternatively :-

=COUNTIF(A1:A10,"piece of data")