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.
This solution is for Excel 365. It will produce the entire combination matrix labels and all.
=LET(
k, MAX(grid),
seq, SEQUENCE(k),
CountCombin, LAMBDA(r, c,
LET(
i, INDEX(seq, r - 1),
j, INDEX(seq, c - 1),
check_weeks, BYROW(grid, LAMBDA(each_week, AND(OR(each_week = i), OR(each_week = j)))),
IF(
AND(r = 1, c = 1),
"",
IF(r = c, "X", IF(AND(r > 1, c = 1), i, IF(AND(r = 1, c > 1), j, SUM(N(check_weeks)))))
)
)
),
MAKEARRAY(k + 1, k + 1, CountCombin)
)
- Wow! I'm beginning to not feel so bad that I couldn't figure it out myself!
Thanks Patrick!
