Use MMULT Instead of SUMIF for Conditional Totals in Sheets

Published on

We typically use multiple SUMIF functions to return individual totals for multiple columns based on a criteria column. Recently, you can automate this process with the BYCOL function in conjunction with SUMIF. Other alternatives include DSUM, which requires structured data, and MMULT. This tutorial explains how to use the MMULT function as an alternative to the SUMIF function for calculating conditional totals in Google Sheets.

Let’s begin with the regular SUM function and its MMULT counterpart.

Using MMULT as a Replacement for SUM in Google Sheets

Assume you have items in A1:A7 and corresponding sales quantities from January to March in B1:D7.

You can use the following SUM formula in cell B8 and drag it to D8 to get the total for each column:

=SUM(B2:B7)

If you don’t want to drag the SUM formula across, you can replace it with the following MMULT formula:

=MMULT(SEQUENCE(1, ROWS(A2:A7), 1, 0), B2:D7)
MMULT Function as a Replacement for SUM

In this, matrix 1 is SEQUENCE(1, ROWS(A2:A7), 1, 0), which returns 1’s horizontally equal to the number of rows in the range. Matrix 2 is B2:D7.

The result of matrix multiplication will be the individual total for each column.

Now, consider the following SUMIF formula:

=SUMIF($A$2:$A$7, "Pear", B2:B7)

If you enter it in cell B9 and drag it to D9, it will return the total quantities for the item “Pear” in each month.

SUMIF Example and MMULT Replacement

You can replace the above SUMIF formula with an MMULT formula for conditional totals across the row. Let’s dive into the details.

MMULT as an Alternative to SUMIF for Conditional Totals: Single Criterion

When applying a condition, you need to modify the criteria column (A2:A7) used in the SUMIF function.

In the previous regular MMULT example, the matrix 1 was SEQUENCE(1, ROWS(A2:A7), 1, 0), which returned the array {1, 1, 1, 1, 1, 1}.

To apply the condition, you replace the value 1 with 0 wherever the criterion “Pear” doesn’t match in A2:A7.

This formula does that effectively: =ArrayFormula(TOROW(N(A2:A7="Pear"))).

  • A2:A7="Pear" returns an array of TRUE or FALSE, where TRUE means the criterion matches.
  • N function converts TRUE to 1 and FALSE to 0.
  • TOROW changes the array from vertical to horizontal orientation.

Now, the matrix will look like this: {0, 0, 1, 0, 1, 0}.

The MMULT formula to replace SUMIF is:

=ArrayFormula(MMULT(TOROW(N(A2:A7="Pear")), B2:D7))

MMULT as an Alternative to SUMIF for Conditional Totals: Multiple Criteria

In the above example, we applied only one condition. Unlike SUMIF, which is dedicated to utilizing a single condition, MMULT allows us to specify multiple conditions.

To get the total for “Pear” and “Apple,” specify matrix 1 as follows:

=ARRAYFORMULA(TOROW((A2:A7="Pear")+(A2:A7="Apple")))

The N function is not required here since the addition converts TRUE to 1 and FALSE to 0.

Thus, the MMULT formula with multiple conditions will be:

=ArrayFormula(MMULT(TOROW((A2:A7="Pear")+(A2:A7="Apple")), B2:D7))
MMULT as an Alternative to SUMIF for Conditional Totals with Multiple Criteria

Resources and References

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

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.