-1

enter image description hereI have a table A having name of people and Table B having name of people and their membership with different organization. Is there any way I can get the total number of membership of each and every people using count function or any way?

user2520291
  • 213
  • 1
  • 2
  • 6
  • 1
    please, show the layout of both tables... if both are in the workbook you are currently working with you could use some function, like `=Countif(...)`. If they are in separate (closed) workbook you could use SQL + ADO to get what you need... – Kazimierz Jawor Jul 06 '13 at 00:19
  • so, you have the answer from @Andy below... – Kazimierz Jawor Jul 06 '13 at 00:27
  • Nop. Actually, @andy counted the no. of times people repeated. I need how many organization that a single people is using. – user2520291 Jul 06 '13 at 00:30
  • @user2520291 .. try in VBA looping .. – matzone Jul 06 '13 at 05:56
  • you need to create your own UDF in VBA using Dictionary... Some helpful links: [first](http://stackoverflow.com/questions/14317051/for-each-loop-on-array-to-return-count-of-each-unique-string/14319581#14319581), [second](http://stackoverflow.com/questions/9663200/excel-vba-to-count-and-print-distinct-values/9678944#9678944), [third](http://stackoverflow.com/questions/17119762/excel-vba-generating-report-based-on-grouping/17123038#17123038), [fourth](http://stackoverflow.com/questions/11761723/count-number-of-different-values-in-chosen-large-range-in-vba/11762259#11762259) – Kazimierz Jawor Jul 06 '13 at 09:00

2 Answers2

1

Is there any way I can get the total number of membership of each and every people using count function or any way?

Did you try a Pivot? See screenshot below

enter image description here I have taken a scenario as you mentioned in Andy G's post... with multiple memberships...

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0

You haven't explained how SQL is involved, but if the two tables are both in Excel then you only need to count the number of times each name occurs in the second table, using COUNTIF and copying this formula down the column:

enter image description here

I'm assuming their membership of an organization is not repeated in the 2nd table.

Added It is more complicated knowing that the combination of person and organization repeats in the second table, but it can be done with an array formula. Use Ctrl-Shift-Enter to complete the following formula, then copy it down the column.

=SUM(IF($E$2:$E$13=A2, 1/(COUNTIFS($E$2:$E$13, A2, $F$2:$F$13, $F$2:$F$13))))

I cannot claim origination for this, a colleague of mine Rudi completed it for me.

Andy G
  • 19,232
  • 5
  • 47
  • 69