calculating the unique combinations

[excelNewbie22]

hi,
i've 3 ranges of numbers,
some overlapping with others,
i want to know how many combinations of 6 i have from all, but unique, with no doubles
so i did this, see example
(took the common between any two ranges and subtract from the sum of all)
but when i'm actually generate all and merge the unique the result is different
in the example below the true result is 392 but i got 385
(and the larger the ranges i'm testing the larger the space between true result and what i got)
where am i wrong?

test.xlsb
	I	J	K	L	M	N	O	P	Q	R	S	T	U
2		1	2	3	4	5	6	7	8	10
3			2	3	4	5	6	7	8	9	10	11
4				3	4	5	6	7	8	9	10	11	15
5
6
7
8
9
10
11
12
13
14
15	9	84
16	10	210
17	10	210
18
19	8	28
20	9	84
21	7	7
22
23		385
6

Cell Formulas
Range	Formula
I15	I15	=COUNTA(J2:R2)
J15:J17,J19:J21	J15	=COMBIN(I15,6)
I16	I16	=COUNTA(K3:T3)
I17	I17	=COUNTA(L4:U4)
I19	I19	=COUNTA(FILTER(J2:R2,COUNTIF(K3:T3,J2:R2)))
I20	I20	=COUNTA(FILTER(L4:U4,COUNTIF(K3:T3,L4:U4)))
I21	I21	=COUNTA(FILTER(L4:U4,COUNTIF(J2:R2,L4:U4)))
J23	J23	=SUM(J15:J17)-SUM(J19:J21)

 

[pgc01]

Hi

If I understand correctly you want the cardinal number of the union of 3 sets.
In this case, you are missing the cardinal of the intersection of the 3 sets.

n(A ∪ B ∪ C) = n(A) + n(B) + n(C) – n(A ∩ B) – n(A ∩ C) – n(B ∩ C) + n(A ∩ B ∩ C)

You are missing the last term

The numbers that are common to all 3 sets are

3, 4, 5, 6, 7, 8, 10

and so you must add

COMBIN(7, 6) = 7

Please test

 

[excelNewbie22]

thanks pgc01

 

[pgc01]

I'm glad it helped. Cheers!