You can use Condition or Criteria in MMULT in Google Sheets similar to SUMIF or SUMPRODUCT. This’s a little tricky. So this time I’m with you with a new tutorial on how to do conditional MMULT in Google Sheets.

If you are new to Google Sheets MMULT function, please first go through my tutorial on the usage of MMULT function in Google Doc Spreadsheets.

Conditional MMULT in Google Sheets

As said above, we can use conditions or criteria in MMULT. You can replace SUMIF or SUMPRODUCT with MMULT in Google Doc Spreadsheets in certain places to return conditional array result.

I hope the below example can shed some light on the usage of MMULT as a replacement to SUMPRODUCT.

Regarding SUMIF, the steps are little different. I’ll post that tutorial later. In concise, you can use criteria or condition in MMULT formula in Google Sheets. Here we go.

Example to Conditional MMULT in Google Sheets:

Formula# 1 in Cell C13:

In this sample dataset, you can see the month wise sales quantity of different fruit items.

The first MMULT formula, a regular one, in C13 returns the month wise sales volume of all fruit items by multiplying the unit rate in column B5: B10 (matrix 1) with quantities in C5: H10 (matrix 2).

Syntax:  MMULT(matrix1, matrix2)

As you may know, the above matrices do not meet the standard, i.e, the number of columns in matrix 1 should be equal to the number of rows in matrix 2.

Here B5:B10 (matrix 1) is a 6 rows X 1 column matrix. As far as C5:H10 (matrix 2) is concerned, it’s a 6 rows x 6 columns matrix.

So we should transpose the first data range B5: B10 to make it a 1-row x 6 columns matrix so that we can match the standard in Matrix calculations.

Here is the MMULT formula in Cell C13.

=mmult(transpose(B5:B10),C5:H10)

Formula# 2 in Cell C15:

The following formula is an example of conditional MMULT in Google Sheets. We are going to use criterion here. Actually, it’s a very simple trick.

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

Here I just want to find the month wise sales volume of only one fruit item, i.e, “Peer”. See that criterion in the MMULT formula above.

Formula Explanation:

Similar to the first MMULT formula, here also the B5: B10 is our first matrix and we should transpose it.

Now the second matrix is an ArrayFormula. If you apply this in any cell, it would return the following values.

This formula creates a virtual Matrix 2 inside MMULT where it retains only the values in the row where A5: A10 value is “Peer”. How it’s possible?

I can give you the further breakdown of this formula.

=ArrayFormula((A5:A10="Peer"))

The above formula returns TRUE or FALSE based on the condition. If the condition “Peer” is met, it returns TRUE, else FALSE.

When multiplying, TRUE is considered as one and FALSE is zero. Hope you can guess why the values in all other rows became zero.

This way you can do conditional MMULT in Google Sheets.

Formula# 3 in Cell C17:

Now let us see how to do the above calculation with SUMPRODUCT. For this, you may want to use multiple SUMPRODUCTs in C17, D17 … H17. Below is the formula in Cell C17.

=SUMPRODUCT(($A$5:$A$10="Peer")*($B$5:$B$10)*(C5:C10))

Here is my Sample Sheet with all the above MMULT formulas live. Copy that sheet and do your own experiment. Enjoy!