Excel MMULT Function

What is MMULT Function in Excel?

The MMULT function is one of the math functions of Excel.

It Returns the matrix product of two arrays, an array with the same number of rows as array1 and columns as array2.

We can find this function in Math & trig category of insert function Tab.

How to use MMULT function in excel

  1. Click on an empty cell (like F5 ).
empty cell in excel

2. Click on fx icon (or press shift+F3).

fx icon in excel

3. In the insert function tab you will see all functions.

insert function tab in excel

4. Select math and trig category.

5. Select MMULT function.

6. Then select ok.

excel MMULT function

7. In the function arguments Tab you will see MMULT function.

8. Array1 is the first array of numbers to multiply and must have the same number of columns as Array2 has rows.

9. Array2 is the second array of numbers to multiply and must have the same number of columns as Array1 has rows.

10. You will see results in the formula result section.

How to use MMULT function in excel

Examples of MMULT function in Excel

Example 1:

How to use MMULT function in excel

You can see examples of MMULT function below:

Examples of MMULT function in Excel
=MMULT({1,2;3,4},{5,6;7,8})------->>>> answer is {19,22;43,50}
=MMULT({1,0;0,1},{0,1;1,0})------->>>> answer is {0,1;1,0}
=MMULT({1,2;2,1},{2,1;1,2})------->>>> answer is {4,5;5,4}

Example 2:

How to count rows with specific value?

By using MMULT function, we can count a specific record in the table.

For example, in the table below, How many people weigh more than 230 kg?

NameAgeHeightWeight
Olivia25210180
Noah25205235
Oliver27195205
Elijah22198185
James29199231
William29201240
Benjamin21202235
Lucas25200238
Henry22204190
=SUM(--(D2:D10>230))---->>>>>answer is 5

For example, in the table above, How many people weigh or Height is more than 200 kg?

=SUM(--(MMULT(--(B2:E10>200),TRANSPOSE(COLUMN(B2:E10)))>0))
answer is 8

What is the purpose of MMULT function?

It Returns the matrix product of two arrays, an array with the same number of rows as array1 and columns as array2 that sometimes called the “dot product”.

What is the Return value of MMULT function?

It returns an array of numbers.

MMULT(array) = array

How many arguments does MMULT function have?

This function has just 1 Argument.

The argument of MMULT function is number.

Array1 is the first array of numbers to multiply and must have the same number of columns as Array2 has rows.

Array2 is the second array of numbers to multiply and must have the same number of columns as Array1 has rows.

The argument of MMULT function is required and not optional.

MMULT(number) = number

Which version of excel supports MMULT function?

This function is available for all excel versions (2003-2019).

Errors in MMULT function

MMULT returns the #VALUE! error when:

  1. Any cells are empty or contain text
Errors in MMULT function
=MMULT({,2;3,4},{5,6;7,8})------->>>> answer is #VALUE! 
=MMULT({1,0;0,},{0,1;1,0})------->>>> answer is #VALUE! 
=MMULT({1,2;,1},{2,1;1,2})------->>>> answer is #VALUE! 

2. The number of columns in array1 is different from the number of rows in array2.

Errors in MMULT function
=MMULT(A3:B4,D3:E3)------->>>> answer is #VALUE! 
=MMULT(A6:A7,D6:E7)------->>>> answer is #VALUE! 
=MMULT(A9:B10,D9:E11)------->>>> answer is #VALUE! 

MMULT related functions :

Leave a Reply

Your email address will not be published. Required fields are marked *