15

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
  • 27,064
  • 61
  • 75
  • 116
user194076
  • 353
  • 3
  • 4
  • 9
  • 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. – Service Desk Bunny Feb 22 '18 at 06: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 Sinha Feb 22 '18 at 11:55

3 Answers3

21

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
  • 356
  • 1
  • 6
4

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
  • 8,995
  • 6
  • 15
  • 35
  • @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 Sinha 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 Sinha Feb 23 '18 at 05:44
-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