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.
karyn90 Just for fun, here's another possibility using the new PIVOTBY function:
=LET(
data, A2:E12,
nums, DROP(data,,1),
UNPVT, LAMBDA(area,TOCOL(IFS(nums<>"",area),2)),
weeks, UNPVT(TAKE(data,,1)),
pivot, DROP(PIVOTBY(weeks,UNPVT(nums),weeks,ROWS,0,0,,0),,1),
items, TAKE(pivot,1),
vals, IFERROR(--DROP(pivot,1),0),
pairs, IF(items<>TOCOL(items),MMULT(TRANSPOSE(vals),vals),""),
VSTACK(HSTACK("Pairs",items),HSTACK(TOCOL(items),pairs))
)
If you don't have PIVOTBY yet, it should be coming very soon...