where data is the named range C5:I13. The result is an array of with nine sums, one for each row in the range, as seen in column K.
When working with Excel formulas, there are some situations where you want an array of sums rather than a single sum. Functions like SUM won't work, because they aggregate results and return a single value. The MMULT function, which returns the matrix product of two arrays (also called the "dot product") is useful in these cases.
In this example, the goal is to return an array with nine subtotals, one for each of the colors named in column B. We can do this with matrix multiplication using the MMULT function. MMULT takes two arrays, array1 and array2, and requires that the number of columns in array1 be the same as the number of rows in array2. The resulting matrix will have same number of rows as the first matrix, and the same number of columns as the second matrix.
The first array is simply all the data in the data, the named range C5:I13:
We use a double negative here to protect against blank cells, which will cause MMULT to throw #VALUE! error. The double negative forces any empty cells to be evaluated as zero.
The first array contains 7 columns, so we need the second array to contain 7 rows. We want just a single column of results, so the second array should be 7 rows by 1 column. Finally, because we don't want to change any original values, the array should contain only the number 1 (i.e. multiplying by 1 does not change the original value). The SEQUENCE function provides an easy way to construct this array, with a bit of help from the COLUMNS function:
Notice the start value in SEQUENCE is 1, but the step value is zero. The result is a 7 x 1 array filled only with 1s. With these two arrays, the MMULT function calculates the matrix product of the two arrays and returns an array containing 7 sums, one for each row in the data:
When working with Excel formulas, there are some situations where you want an array of sums rather than a single sum. Functions like SUM won't work, because they aggregate results and return a single value. The MMULT function , which returns the...
The Excel COLUMNS function returns the count of columns in a given reference. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.