Grouping a column and finding count of each entry

[ankita.sethi]

Hi,

In a particular column of an excel sheet, I want a formula (VBA or worksheet) gives the count of each enrty in that column.

let's say column name is Animals

Animals
Cat
Dog
Dog
Dog
Cat
Rabbit

That Formula should be able to tell me #Cat = 2, #Dog = 3, #Rabbit =1.

Is this possible to do with sumproduct by any chance?

Please advice! This will make my life much simpler!

 

[Rick Rothstein]

Probably not exactly what you are after, but here is a simple solution. Assuming your data is in Column A, put this in B2 (the cell after the header row) and copy it down...

=IF(COUNTIF(A$1:A2,A2)=1,COUNTIF(A:A,A2),"")

The total count of each animal will appear next to the first occurence of that animal's name in the column.

 

[ankita.sethi]

Hi, thanks for the reply!

However, what I would like is for a sort of a table to be created with two columns - one with unique values and second with their respective counts.
(I hope I am making sense :P ; if not, please let me know and I will try to be more elaborate)

Thanks a lot!

 

[Rick Rothstein]

I think the set up and formulas described in this blog article do what you are asking for...

http://www.get-digital-help.com/200...-and-sort-by-occurrances-from-large-to-small/