HomeGoogle DocsSpreadsheetHow To Use MMULT Function in Google Sheets

How To Use MMULT Function in Google Sheets [Practical Example]

Published on

Here we can learn A to Z about Google Sheets MMULT function. This’s an array function which is related to multiplying Matrices. So let us learn what is Matrix and how to use MMULT function in Google Sheets to multiply Matrices.

First, let me take you to the syntax part of this spreadsheet formula. After that, take time to understand what is math’s matrix with the help of an example.

Finally, I have included one real-life example to the use of MMULT formula in Google Sheets.

Syntax to Google Sheets MMULT Function

Syntax:

MMULT(matrix1, matrix2)

matrix1 – The first matrix in the matrix multiplication operation. It must be represented as an array/range.

matrix2 – The second matrix in the matrix multiplication operation. It must be represented as an array/range.

Here you should take care of the following. Please stick to the below standard and I’ll explain what it is related to.

The number of columns in Matrix1 should be equal to the number of rows in Matrix2

Learn the MATRIX for MMULT in Google Sheets

See two Matrices below which follows the above standard. Here the number of columns in the Matrix 1 is 3 which is equal to the number of rows in the second Matrix.

how to multiply two matrices - spreadsheet tricks

Now let us see how to multiply the above two Matrices.

There are four steps involved. First multiply each value in the Matrix 1 row 1 (there is only one row) with each value in the Matrix 2, column 1.

The below screenshot depicts this.

MMULT visualisation

Then repeat the same for column 2,

3 x 9 + 4 x 7 + 2 x 4 = 63

colum 3,

3 x 7 + 4 x 4 + 2 x 0 = 37

and column 4.

3 x 15 + 4 x 6 + 2 x 3 = 75

This way you can multiply matrices. In real-life this will come useful. I’ll come to that later.

Now it’s time to automate the Matrix multiplication with the help of Google Sheets MMULT formula. Here we go!

How To Use MMULT Function in Google Sheets

If you could understand the above Matrix multiplication concept, using MMULT is kids play. Just select the range containing Matrix1 and Matrix2 and separate it with Comma in MMULT.

Let’s see the MMULT formula for the above two Matrices. You can see that the following MMULT formula result is matching with our above manual calculation.

basic MMULT formula in Google Sheets

Point of Interest:

The MMULT formula result will obviously retain the same number of rows as per the matrix 1, and the same number of columns as per the matrix 2.

Here see the first value, i.e. 83, in the result. It’s the multiplication of the values 3 x 13 + 4 x 8 + 2 x 6.

If you know Google Sheets TRANSPOSE as well as SUMPRODUCT functions, then you can easily understand the below formula.

The below Sumproduct comparison to MMULT formula would return the value 83.

=SUMPRODUCT(transpose(B3:D3),G4:G6)

Once again understand the MMULT formula usage with the help of this image. In both examples, matrix 1 in cyan color and matrix 2 in green color, see how the multiplication operation has taken place.

MMULT new example - Sheets

Practical Example to the Use of MMULT Function in Google Sheets

Here is one MMULT formula to make you understand why MMULT is a necessary function in Google Sheets. It’s a real-life example.

Sample Data:

Sample data for MMULT practical use in Google Sheets

Here I have got the sales quantities of 6 products for a period of 6 months. In the above, I’ve marked the range B4:G9 which is our 6 rows x 6 columns Matrix 2.

I have the following rates for the 6 products spread across the range B16:G16. You can consider this range as our 1-row x 6 columns Matrix 1.

Here you may please take a note that the 6 columns in Matrix 1 are matching with 6 rows in Matrix 2. That means the two arrays follow the Matrix standard. It happens naturally.

We can use here Google Sheets MMULT formula to find the month wise sales value of all the products.

MMULT in practical use - example screenshot

Follow this tutorial by applying the above examples in a Google Sheets file. Then you can easily learn the MMULT concept and Matrix.

Related Tutorials:

  1. MMULT Instead of SUMIF in Google Sheets for Array Result.
  2. How to Do Conditional MMULT in Google Sheets [MMULT with Criterion]
  3. Array Formula to Conditionally Sum Date Ranges in Google Sheets.
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...

LEAVE A REPLY

Please enter your comment!
Please enter your name here