Forum Discussion
MMULT to pivot data totals rather than SUMIFS
In this post, SergeiBaklan explained how to use MMULT rather than SUMIF when the input data is an array rather than a range. How does this extend to pivoting summed data like SUMIFS does when the inp...
I figured this out, for anyone who might need it. For me this was important because now I can completely escape pivot tables and format financial statements exactly as I want.
You create a matrix of the items that match one of the criteria, multiplied by the count, to get Count or 0. Then you make a matrix of the items that match the second criteria that is just 1 or 0. Then multiply the matrices to get Count or 0 at each intersection of the two criteria.
=LET(
sold,{3;6;2;1;4},
item,{"a";"b";"a";"c";"a"},
color,{"red";"blue";"blue";"orange";"red"},
rows,UNIQUE(item),
cols,UNIQUE(TRANSPOSE(color),1),
rowmatch,--(rows=TRANSPOSE(item))*TRANSPOSE(sold),
colmatch,--(cols=color),
countmatch,MMULT(rowmatch,colmatch),
countmatch
)
Hope one day Microsoft introduces the function which does such stuff natively