Count with grouping in Excel

I want to count a number of rows for each value appearing in a column. Is there an easy way to do this in excel?

Let's say I have the following table:

A B C
1 Cat A
3 Cat A
3 Cat B
4 Dog A
5 Cat B

I want to get the following result:

B Row_Count
Cat 4
Dog 1

Also, it would be nice to be able to SUM

B SUM_Col_A
Cat 12
Dog 4

But I cannot specify strings "Cat" and "Dog" explicitly because my columns contain >1000 distinct values.

3 Answers

Many ways.. simplest one (to my view) is to use a pivot table but it depends on your needs..

If you define your data table with a name then it will expand automatically as you add value and a refresh on the pivot will update the numbers

To find the Grand Total of Cat & Dog use the following Formula.

=SUMIF($B$2:$B$6,$B$8,$A$2:$A$6)

=SUMIF($B$2:$B$6,$B$9,$A$2:$A$6)

To find the Total of Cat & Dog use these Formula.

=COUNTIF($B$2:$B$6,$B$11)

=COUNTIF($B$2:$B$6,$B$12)

Check the screen shot.

I do hope this help you.

Step-1:You can copy B column to a new sheet and apply remove duplicates Step-2: use countif and sumif formula on them