Sumproduct Help Please

  • Thread starter Thread starter Paul Black
  • Start date Start date
P

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
 
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)
 
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
 
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)
 
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
 
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)
 
Brilliant Bob ( BIG Thumbs Up ), that does EXACTLY what I Require.
Thank You VERY Much.

All the Best.
Paul
 
Glad we got there.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Back
Top