This tutorial is in line with my earlier tutorial where I’ve detailed the use of criteria in MMULT. In that tutorial, I’ve explained how to use criterion in MMULT especially as equal to SUMPRODUCT and return an array result. This time, you can learn how to use MMULT instead of SUMIF in Google Sheets.

In this usage, the MMULT formula has an edge over SUMIF as it can expand to rows. Learn this awesome trick in this Google Doc Spreadsheets tutorial.

Similar: How to Do Conditional MMULT in Google Sheets (a replacement to Sumprodut to expand the result)

How to Use MMULT Instead of SUMIF in Google Sheets

First, make sure that you know the usage of SUMIF in Google Sheets though it has no relation with our MMULT formula that follows. Let’ start.

Example:

With this example, you can learn how to use MMULT instead of SUMIF in Google Sheets for Array Result.

First a basic SUMIF formula to SUM the Qty. of “Peer” fruit in column A.

=SUMIF($A$5:$A$10,"Peer",C5:C10)

This formula would sum Column C if Column A value is “Peer”. There is a drawback in the use of this formula. what’s that?

If you want to sum Column D, then additional Sumif is required. To understand this, see the below screenshot.

To get the Column D total for “Peer”, you should copy paste the formula in C21 to D21 and continue for other columns. But an MMULT formula can change this scenario.

With MMULT you only need to apply the formula in C21 and it would expand to H21. How? First, see that MMULT formula to return array result as an alternative to SUMIF.

=mmult(transpose(ArrayFormula(row(B5:B10)^0)),ARRAYFORMULA((A5:A10="Peer")*C5:H10))

Now let’s move to the formula explanation part. There are two matrices in this formula and it’s a standard in MMULT. What are they?

Matrix 1:

=transpose(ArrayFormula(row(B5:B10)^0))

When you use ROW function with the array in a range as above, it returns the row numbers.

Here the returned number would be the row number 5, 6, 7, 8, 9 and 10 in column wise. You can’t use this column in MMULT as it would return incompatible matrix size error.

Remember the MMULT standard.

The number of columns in matrix 1 should be equal to the number of rows in matrix 2.

So we have to use transpose to make the matrix in order. Further, if we use the above row number we would get a wrong result as multiplication involved.

So I’ve used the caret ^ symbol to raise the value to 0. That means the row number would be converted to 1 as per the screenshot above.

Matrix 2:

=ARRAYFORMULA((A5:A10="Peer")*C5:H10)

This formula up to the multiplication part returns a TRUE or FALSE array result. If the fruit name in A5: A10 is “Peer” it returns TRUE in that row else FALSE. Here Row 5 and 9 satisfy this condition.

Again when you multiply the same with C5: C10 it returns the value (Qty.) from the original range wherever TRUE appears as TRUE is considered as 1 and FALSE as 0 in multiplication.

The above two matrices in the MMULT would return a SUMIF similar result. That’s all. Hope you have liked the above tips. It’s simple to understand if you apply the above formulas in your own Sheet.