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
)
You are brilliant! I'd been fighting with the same issue, where SUMIFS worked with cell ranges, but not with named arrays. Your "find" in the MMULT space worked perfectly for me.
I now have complete control, with a single cell LET function over the full report! Freedom from the constraints of PIVOT TABLES!!Thanks for taking the time to come back to your own query and publishing a solution...
Hope one day Microsoft introduces the function which does such stuff natively
