To get an array of coilumn totals based on a range of numeric values, you can use a formula based on the MMULT function, with help from SEQUENCE and COLUMNS. In the example shown, the formula in K5 is:
where data is the named range C5:I13. The result is an array of with seven sums, one for each column in the range, as seen in row 15.
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 seven subtotals, one for each of the days named in row 4. 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.
In the example, we want just a single row of totals, so the first array needs to contain just one row. Also, because we don't want to change any original values, this array should contain only the number 1 (i.e. multiplying by 1 does not change the original value). The SEQUENCE function provides an convenient way to construct the array we need, with a bit of help from the ROWS function:
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. With these two arrays, the MMULT function calculates the matrix product, and returns an array containing 7 sums, one for each column in the data:
These values are returned to cell C15, and spill into the range C15:I15.
In older versions of Excel that do not have the SEQUENCE funcion, you may see an different syntax for this formula based on the TRANSPOSE function:
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.