Count based on group membership

Hi everyone, I would like to get the number of countries that a given "Name" has Partners in. (See example sheet for an illustration)
I have the ID of a group in column A. Column B represents Names. Column C gives Info about the country of "Name".
In column D I would like to get the information how many unique countries a "Name"'s group members are from. This means, not only within a single group, but in total over all groups. This means, that every same Name has the same number of countries in column D.

Does anyone know how to do this?

Formula in words:
"sum of all unique countries in column C, if ID in column A matches any ID of Name in column B"

Thanks a lot in advance!

The Formula for column D gives me a "1" all the time. Accordingly, the formula for column E adds 1 even if I add another partner with from the same country (e.g., if I add a new row for group/ID 1, Name H, Country GER, the count still goes up by one, even though, GER is already included).

Ah, now I see, I am so sorry. The example sheet is wrong for reasons that are a little unclear to me, since I uploaded the file again...

Now this should all make more sense I guess. Sorry again

I hope, that you are talking about column D now... .
Please see the attached file for the expected result and the "explanation".
Basically, it shall start with a name (column B), look up all the IDs, that belong to a given name, then check all the other Names, that share an ID with the given name, and "create" a list of all the countries that the partners of the given name are from. Then it shall "remove all duplicates".

Here, try this with helper column:

Difference is that mine result return 1 for AUS while you wrote 2

Please Login or Register  to view this content.

But both /4 and 6) has CH & F so that should be 1 unique, as I got. Right?

I am sorry, that my explanations seem to be confusing. Whereas I do not understand your formula zbor, I seems as if it might work eventually. So I will try to explain again in (hopefully) simpler terms :p.

- IDs (column A) are identifiers for groups, i.e. all firms with the same ID are members of the same group (e.g. A and B are in a group; C and D; A and C; D, E and F and so on)
- Name (column B) are names for different firms
- Country (column c) is the country in which the firm is located
- I am always looking from the perspective of a given focal firm (i.e. the firm in the respective row)
- this firm is a member of some groups (i.e. has some IDs)
- in these groups, there are - by definition - other firms (i.e. those firms, that share an ID with the focal firm), let's call them alter firms
- these alter firms are located in different countries
- I would like to know, how many different countries the alter firms of the focal firm come from, and by this I mean unique countries (e.g., if Firm X is a member in 2 groups, and in both groups the alter firm comes from the same country, this adds just "1")

Let me know at which step of the argumentation you "lose me" .

In your example zbor, I believe you used the country as a basis, whereas it should be the name.

First let me answer of above:

But both (4 and 6) has CH & F so that should be 1 unique, as I got. Right?

so I know is mine logic right or not

Originally Posted by zbor

First let me answer of above: so I know is mine logic right or not

Yes, if you are looking from the perspective of firm E, you are right, because it doesn't matter that in one case it is firm C and in the other F that is from F(rance). But as E has connections to two different countries (CH and F), the result for E should be "2" (just for IDs 4 and 6).

Originally Posted by daddylonglegs

This "array formula" in row 2 will give the exact results you expect:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(A$2:A$15,IF(B$2:B$15=B2,A$2:A$15),0)),MATCH(C$2:C$15,C$2:C$15,0)),ROW(C$2:C$15)-ROW(C$2)+1),1))-1

confirm with CTRL+SHIFT+ENTER and copy down - see yellow section in attached

Hey daddylonglegs,
thanks a lot this seems to work. I do not understand how exactly the formula works (I still don't conceptually understand how to deal with formulas, where arguments are missing...), but I ran some cross checks and changed entries and it seems to work as intended. Thanks a lot everyone!

Just a quick question, though:
the "-1" at the end shall prevent counting a firm's own country, right? If a firm has a partner from the same country as its own country, this isn't counted now, is it?

Sorry, daddylonglegs, for coming back so late to you.
Thanks for the clarification, I guess I do not need the formula adjustment as based on theory a partner from the same country should not bring any additional "country-specific" knowledge.