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.

• You can use the Consolidate option (Data->Data Tools->Consolidate). First select the A&B columns and use Consolidate with COUNT and then with SUM. – User552853 Feb 22 '18 at 6:21
• @User194076,, check I've posted a solution, if you can handle VBA code then for the faster process I can suggest you bunch of codes since you have written that you have more that 1000 Rows. – Rajesh S Feb 22 '18 at 11:55

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.