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

Question

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!

Doug Glancy · Accepted Answer · 2013-08-10 04:02:40Z

1

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

edited Aug 10, 2013 at 4:02

answered Aug 10, 2013 at 3:48

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doug Glancy Over a year ago

I just noticed you asked for VBA, but unless there's some compelling reason, a formula like mine or Ioannis's is preferable.

Ioannis · Accepted Answer · 2013-08-11 02:58:09Z

1

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!

edited Aug 11, 2013 at 2:58

answered Aug 10, 2013 at 3:44

Ioannis

5,4682 gold badges21 silver badges32 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related