Count the number of shared names by other groups

Dear all,

I made an example here for my problem. There are 4 groups in columns A:D, which are named as G_1, G_2, G_3, G_4.

In cell G1, I use the formula below to calculate the number of names in group G_1 that are also exist in group G_2:

Please Login or Register  to view this content.

Now my question is: how to find the number of names that are shared by ALL OTHER groups (i.e. not just a group)?

I think:
=SUM(IF(COUNTIF(B2:B8,A2:A8)>0,1,0)*IF(COUNTIF(C2:C8,A2:A8)>0,1,0)*IF(COUNTIF(D2:D8,A2:A8)>0,1,0))
as an array formula (confirm with ctrl+shift+enter) would give you the number of names in all four groups.

Hi ragulduy, is there a way to use the named ranges? I tried something like:

Please Login or Register  to view this content.

But it did not work.

Is there any one who can provide a nice answer? My former formula does not work

Thanks in advance.

=SUM(IF(COUNTIF(INDIRECT(F2),INDIRECT(F1))>0,1,0)*IF(COUNTIF(INDIRECT(F3),INDIRECT(F1))>0,1,0)*IF(COUNTIF(INDIRECT(F4),INDIRECT(F1))>0,1,0))

Still an array formula.

Thanks for the reply. It will work. But is there a way that we can simplify the formula above?

Assume that there are 100 names in cell F1, F2, ... F100, it will be difficult to use the individual form above. Is it possible to combine them as, for example, F1:F100?

Sorry for the question.

If each name will only appear in each list a maximum of one time you could do it like this:
=SUM(IF(COUNTIF(A2:D8,A2:A8)=4,1,0))
but I don't know how to do that with the named range as indirect

I could give you a vba solution but I'll see if anyone has an idea how to do it with formulas.

My question is: the formula provided by ragulduy will work. But I don't want to repeat so many times for each of the components when there are say 100 groups (or named ranges). I hope to have some formula like ={SUM(IF(INDIRECT(F1)="","",IF(COUNTIF(INDIRECT(F2:F100),INDIRECT(F1))=0,0,1)))}, where cells of F1, ... F100 contain all the named ranges. Maybe this is difficult and impossible?

Hi XOR LX, thanks for your reply. That is correct. Cells F1, F2 specify the named ranges to be used.

@xor

The problem I had was how to define the ranges using indirect.

So f1-f4 contains the names of the ranges a1-a4,b1-b4,c1-c4 etc ... How do you apply the countif to range a1-d4 by building it from the individual names in column f?

Thanks for trying to help me out. If it is really impossible, then I just close the post to save your time. Much appreciated.

Hi XOR LX,

For my particular problem, named ranges must be used. So I made an example and attached to show what I want to achieve. Say, there are 4 columns of data A, B, C and D. We name them as 4 groups respectively using the named ranges as: Group_1, Group_2, Group_3 and Group_4. Now we type these named ranges in cells F1, F2, F3 and F4 respectively. Then the formula provided by ragulduy can be used to check how many cases of Group_1 also exist in the other groups (that is, to check how many cases are not unique to Group_1):

=SUM(IF(COUNTIF(INDIRECT(F2),INDIRECT(F1))>0,1,0)*IF(COUNTIF(INDIRECT(F3),INDIRECT(F1))>0,1,0)*IF(COUNTIF(INDIRECT(F4),INDIRECT(F1))>0,1,0))

While this formula does work, the trouble is: if I have 100 named ranges like that for 100 groups, then I need to repeat tying the same formula for 100 times which is not ideal. That is why I hope to have a nice formula which combines them like F1:F100 to make life easy

Finally the formula provided by icestationzbra does work. I may need to spend dozens of hours to digest this formula, looks very advanced

Thanks for help, everyone.

Points taken, XOR LX. I'm still learning but glad to learn