Sumproduct Help Please

[Paul Black]

Hi Everyone,

I have 6 Numbers in Cells "J16:O16" ( Numbers 1 2 3 4 19 24 for Example
).
I have another set of Numbers in Cells "D20:G20" ( Numbers 2 19 24 for
Example ).
I would like to Count how many Occurances Matched 3, 2, 1 & 0 Numbers
Seperately Please.

I have the Formula in Cell "X20" ...

=SUMPRODUCT(--(COUNTIF($J$16:$O$16,$D20:$G20)))

.... which gives me the Result 3, because Numbers 2 19 & 24 Matched. In
Fact I only want it to give me the Result 1.

The Result for 2 Matching ( in Cell "X25" for Example ) is 3, Numbers
....

2 19
2 24
19 24

The Result for 1 Matching ( in Cell "X30" for Example ) is 3, Numbers
....

2
19
24

Any Help will be Greatly Appreciated.
All the Best.
Paul

 

[Bob Phillips]

Paul, do you mean

=COUNTIF($J$16:$O$16,D$20)

and then copy across.


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Paul Black]

Thanks for the Reply Bob,

Within the 6 Numbers 1 2 3 4 19 & 24 and my Second set of Numbers 2 19
& 24 there are 3 Matching ( in this Particular Case ) Pairs ...

Pair 1 = 2 19
Pair 2 = 2 24
Pair 3 = 19 24

.... so I would like the Resulting Formula to Return 3 ( 1 for Each Pair
Matched ) Please.

Thanks in Advance.
All the Best.
Paul

 

[Bob Phillips]

Hang on. Didn't you ay in the OP that it DOES give 3 (which it did in my
tests), and that you wanted 1?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Paul Black]

You are quite right Bob. That was for the Triple.
For the Pairs Matched, if the Set of Numbers in Cells "D20:G20" was 2
19 30 instead of 2 19 24, then the Resulting Formula would have to
Return 1, being Numbers 2 & 19 because there was only 1 Pair that
Matched out of the 3 Pairs ( Originally Pair 1 = 2 19, Pair 2 = 2 24,
Pair 3 = 19 24 ) available from the Numbers in Cells "D20:G20" to the 6
Numbers in Cells "J16:O16".

Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Hi Bob,

I Hope I have Explained this Clear Enough.

All the Best.
Paul

 

[Bob Phillips]

Not sure Paul, but here is another shot

=COMBIN(SUMPRODUCT(--(ISNUMBER(MATCH(D20:F20,J16:O16,0)))),2)

where the ,2 at the end signifies a double, ,3 for a triple.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Paul Black]

Brilliant Bob ( BIG Thumbs Up ), that does EXACTLY what I Require.
Thank You VERY Much.

All the Best.
Paul

 

[Bob Phillips]

Glad we got there.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)