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 Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

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.