| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| Sumproduct | Excel Discussion (Misc queries) | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| Sumproduct | Excel Discussion (Misc queries) | |||
Linear Mode
