Sumproduct Help Please
 

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

Sumproduct Help Please
 

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" wrote in message
oups.com...
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

Sumproduct Help Please
 

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

Sumproduct Help Please
 

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" wrote in message
ups.com...
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

Sumproduct Help Please
 

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

Sumproduct Help Please
 

Hi Bob,

I Hope I have Explained this Clear Enough.

All the Best.
Paul

Paul Black wrote:
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

Sumproduct Help Please
 

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" wrote in message
oups.com...
Hi Bob,

I Hope I have Explained this Clear Enough.

All the Best.
Paul

Paul Black wrote:
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

Sumproduct Help Please
 

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

All the Best.
Paul

Bob Phillips wrote:

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" wrote in message
oups.com...
Hi Bob,

I Hope I have Explained this Clear Enough.

All the Best.
Paul

Paul Black wrote:
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

Sumproduct Help Please
 

Glad we got there.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Paul Black" wrote in message
ups.com...
Brilliant Bob ( BIG Thumbs Up ), that does EXACTLY what I Require.
Thank You VERY Much.

All the Best.
Paul

Bob Phillips wrote:

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" wrote in message
oups.com...
Hi Bob,

I Hope I have Explained this Clear Enough.

All the Best.
Paul

Paul Black wrote:
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