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.

user194076

Posted 2018-02-22T03:35:23.367

Reputation: 253

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 – 2018-02-22T06:21:06.600

@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 – 2018-02-22T11:55:48.753

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

R. Prost

Posted 2018-02-22T03:35:23.367

Reputation: 226

1It would be more useful if you provide the step how to create a "pivot table" – The Godfather – 2019-09-06T16:33:41.227

Something about pivot table: https://www.excel-easy.com/data-analysis/pivot-tables.html

– hxysayhi – 2019-11-05T10:59:52.660

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.

Rajesh S

Posted 2018-02-22T03:35:23.367

Reputation: 6 800

@User194076,, check this solution and if you can handle the VBA code then I can suggest you bunch of codes for the faster process, since you have written that you have more that 1000 Rows. – Rajesh S – 2018-02-22T11:56:57.517

Thank you for your suggestion but I don't think this will work since Cat and Dog are hardcoded in formula. – user194076 – 2018-02-22T22:31:39.483

@user194076,, now check,, I've made the Formula dynamic,, simply replaced the criteria with their respective cell address :-) . – Rajesh S – 2018-02-23T05:44:24.630

-3

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

Posted 2018-02-22T03:35:23.367

Reputation: 1