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.
Yet another approach! I used the FREQUENCY function to assign the numbers to the appropriate bin and an outer product to identify pairs of values.
PAIRSλ = LAMBDA(rowϑ,
LET(
bins, SEQUENCE(9),
freq, FREQUENCY(rowϑ(), bins),
SIGN(freq * TOROW(freq))
));
THUNK = LAMBDA(x, LAMBDA(x));The function generates a 10x10 array showing the pairings for week 1. Note that the row data is passed as a thunk. This allows REDUCE to pass the data rows directly, rather than passing the week number and looking up the row (either approach is OK).
= LET(
rowsϑ, BYROW(data, THUNK),
init, MUNIT(10),
result, REDUCE(init, rowsϑ, LAMBDA(acc,rϑ, acc + PAIRSλ(rϑ))),
IF(init, "", result)
)