MMULT Instead of SUMIF in Google Sheets for Array Result

0
150
MMULT Instead of SUMIF in Google Sheets for Array Result

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:

mmult as sumif example 1

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. If you want to sum Column D, then additional Sumif is required. To understand this, see the below screenshot.

normal sumif drawback compared to mmult

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.

Formula: =mmult(transpose(ArrayFormula(row(B5:B10)^0)),ARRAYFORMULA((A5:A10=”Peer”)*C5:H10))
Note: if you copy from here, re type all double quotes.

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

matrix 1 in mmult instead of sumif

When you use ROW function with 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 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)

matrix 2 sumif and mmult

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 liked the above tips. It’s simple if you apply the above piece of formulas in a spreadsheet and practice can make you perfect.

LEAVE A REPLY

Please enter your comment!
Please enter your name here