The MMULT function takes two arguments, array1 and array2, both of which are required. The column count of array1 must equal the row count of array2. For example, you can multiply a 2 x 3 array by a 3 x 2 array to return a 2 x 2 array result. The MMULT function will return a #VALUE! error if array1 columns do not equal array2 rows. Note: In Excel 365, which supports dynamic arrays, MMULT spills multiple values on the worksheet. In earlier versions, you will need to enter as a multi-cell array formula with control + shift + enter.
Example #1 - basic usage
In the example shown, the MMULT formula is evaluated like this:
Example #2 - count rows with specific value
In this example, the goal is to count rows that contain the number 90. The challenge is that the value might appear in any of several columns, and might appear in more than one column of the same row. The MMULT function is used to condense results from multiple columns into a single 1-column array that can then be summed with the SUM function. The formula in G5 is:
Read a detailed explanation here. See below for more examples.
Notes
Arrays must contain numbers only. Columns in array1 must equal the rows in array2. Array1 and array2 can be provided as cell ranges, array constants, or references. MMULT returns #VALUE! if any cells in array1 and array2 are not numbers MMULT returns #VALUE! if array1 columns do not equal array2 rows.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
