Forum Discussion
Solved
Counting instances of specific data pairs across entire array
My source table looks like this: Court 1 Court 1 Court 1 Court 1 WEEK 1 1 6 2 7 WEEK 2 5 9 1 7 WEEK 3 3 10 1 5 WEEK 4 6 10 4 5 WEEK 5 8 10 7 3 WEEK...
=IF(C$15=$B16,"-----",SUMPRODUCT((N(MMULT(N($B16=$B$2:$E$12),ROW($1:$4))>0))*(N(MMULT(N(C$15=$B$2:$E$12),ROW($1:$4))>0))))
This works in my sheet if i correctly understand what you are looking for.
What do you do with combinations like 9 and 2 vs 10 and 1 vs 3 and 8, all pairs equalling 11, an all occurring in your sample source table?
What, if I may be so bold as to ask, is the point of this exercise? Or is the fact that 1 plus 2 equals 3 as well as occurring three times, coincidental (and confusing) in your example?
Are you also wanting the combinations (however we're to end up defining them) to be accounted for below that diagonal as well as above it?
What, if I may be so bold as to ask, is the point of this exercise? Or is the fact that 1 plus 2 equals 3 as well as occurring three times, coincidental (and confusing) in your example?
Are you also wanting the combinations (however we're to end up defining them) to be accounted for below that diagonal as well as above it?
- Yes, please be bold! I'm making a round robin schedule that takes place over many weeks, where there are 4 people assigned to a court and they decide with whom to play. I'm trying to avoid having any 2 people always on the court every week. (I haven't found any on-line schedulers that produce anything viable.)
Any combinations below the diagonal will have already been counted and recorded above the diagonal.
In my example, the circled cell, which is the number of times (or weeks) that 1 and 2 are together , should contain "3". As you point out, the fact that the answer to my example is "3" AND 1+2 equals 3 is merely coincidental (*and* confusing).
As for different pairs summing to the same number (i.e., 9 and 2 and 10 and 1), I am not interested in the sums; I am instead interested in pair itself.
