HomeGoogle DocsSpreadsheetMMULT Instead of SUMIF in Google Sheets for Array Result

MMULT Instead of SUMIF in Google Sheets for Array Result

Published on

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. what’s that?

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.

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

matrix 1 in mmult instead of sumif

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)

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 have liked the above tips. It’s simple to understand if you apply the above formulas in your own Sheet.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

3 COMMENTS

  1. That’s really great, thanks!

    I’ve been searching for two days.

    Is it much more efficient than expending a traditional SUMIF in terms of calculation time?

    Also, would it be possible to do the same to get the average and the standard deviation?

    • Hi, Grange Romain,

      Regarding efficiency, if the data is small, it won’t make much difference. Otherwise, I would prefer the SUMIF over the MMULT.

      There is a much more efficient formula for a larger dataset. It is by using the DSUM database function.

      For that, we should make the data structured. Please refer to my sample data screenshot. In that, I have merged a few cells in the title. Remove them.

      So the field labels “Name” should be in A4 and “Unit Rate” in B4.

      Then you can use the below DSUM instead of the MMULT.

      =ArrayFormula(dsum(A4:H10,{3,4,5,6,7,8},{"Name";"Peer"}))

      For standard deviation, replace DSUM in the above formula with the DSTDEV function.

      =ArrayFormula(dstdev(A4:H10,{3,4,5,6,7,8},{"Name";"Peer"}))

      For average, replace DSUM with DAVERAGE.

      =ArrayFormula(daverage(A4:H10,{3,4,5,6,7,8},{"Name";"Peer"}))

  2. It was helpful!

    I hadn’t been able to expand my row for days. I tried counts, filters, and sums in every way I could think.

    It was an elegant solution, though!

LEAVE A REPLY

Please enter your comment!
Please enter your name here