CODEWITHSUNDEEP
vbaexcel
Erin Santos
Erin Santos

Reputation: 179

VBA Excel Count number of data in a column based on another column

pardon me for my confusing question. Even I don't know how to say it correctly so i'll just illustrate it

Column A          Column B
   A                Mark
   A                
   B                John
   B                Gina
   C               
   C                Kevin
   D                Joy
   D                
   D                Mary

Now, I want to count the number of data on the column B depending on what is on the column A (they are on the same row).

Column C     Column D
  A            1
  B            2
  C            1
  D            2

Thanks!

Upvotes: 1

Views: 835

Answers (2)

Ioannis
Ioannis

Reputation: 5388

try this (untested as I am not on a PC): =SUMPRODUCT((($A$1:$A$9)=C1)*(($B$1:$B$9)<>""&$A$1000))

Any empty cell in place of $A$1000 will do. This assumes that column C is an input, let me know if else. Place this In cell 'D1' and drag it down. Hope it helps, Sorry if I missed something, cannot test at the moment!

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27478

In Excel 2007 and later you can use COUNTIFS. Assuming data in A1:B9:

=COUNTIFS($A$1:$A$9,C1,$B$1:$B$9,"<>"& "")

enter image description here

Upvotes: 1

Related Questions