18

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.

fixer1234
  • 28,064
user194076
  • 383

3 Answers3

24

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

enter image description here

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

R. Prost
  • 386
5

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.

enter image description here

I do hope this help you.

Rajesh Sinha
  • 9,403
-4

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

Naveenkumar
  • 1