Forum Discussion

boukasa's avatar
boukasa
Brass Contributor
Feb 01, 2023

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 input is an array?


Example - say I have this data at A1:

 

itemcolorsold
ared3
bblue6
ablue2
corange1
ared4

 

I can pivot the summed data like this:

 

 

=LET(
  sold,C2:C6,
  item,A2:A6,
  color, B2:B6,
  rows,UNIQUE(item),
  cols,UNIQUE(TRANSPOSE(B2:B6),1),
  SUMIFS(sold,item,rows,color,cols)
)

 

 

 

720
060
001

 

But if the columns are arrays, SUMIFS won't do:

 

 

=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),
  r,SUMIFS(sold,item,rows,color,cols),
  r
)

 

 

#VALUE!#VALUE!#VALUE!
#VALUE!#VALUE!#VALUE!
#VALUE!#VALUE!#VALUE!
#VALUE!#VALUE!#VALUE!
#VALUE!#VALUE!#VALUE!

 

I see how to use MMULT to sum the rows and the columns, and how to count the number of matching items:

 

 

=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),

  prodmatch,--(rows=TRANSPOSE(item)),
  prodsum,MMULT(prodmatch, sold),

  colormatch,--(cols=color),
  colorsum, MMULT(TRANSPOSE(sold),colormatch),

  countmatch,MMULT(prodmatch,colormatch),
  countmatch
)

 

 

210
010
001

 

But how do I sum the matching items?

 

Thanks for any help.

excel
Formulas and Functions
  • boukasa's avatar
    boukasa
    Brass Contributor
    Feb 02, 2023

    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
)

     

    • bstrib's avatar
      bstrib
      Copper Contributor
      Apr 08, 2023

      boukasa

       

      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...

Resources