5

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
10

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

1

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.

  • @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 Feb 22 '18 at 11:56
  • Thank you for your suggestion but I don't think this will work since Cat and Dog are hardcoded in formula. – user194076 Feb 22 '18 at 22:31
  • @user194076,, now check,, I've made the Formula dynamic,, simply replaced the criteria with their respective cell address :-) . – Rajesh S Feb 23 '18 at 5:44
-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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.