Quick, clean, and to the point

Get column totals

Excel formula: Get column totals
Generic formula 

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:

SEQUENCE(1,ROWS(data),1,0) // returns {1,1,1,1,1,1,1,1,1}

Notice rows is set to 1, columns is 9 (from ROWS), start value is 1, and step value is zero. The result is a 1 x 7 array filled only with 1s.

For the second array, we use the full set of values in the named range data (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. 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.

Alternate syntax

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:


While slightly more cryptic, this syntax is a clever way to accomplish the same thing. Raising any number to the power of zero (0) results in 1, so the ROW function returns 9 numbers:

ROW(data) // returns {5;6;7;8;9;10;11;12;13}

which are raised to power of zero with the exponent operator (^):

ROW(data)^0) // returns {1;1;1;1;1;1;1;1;1}

Finally, TRANSPOSE flips the array:

TRANSPOSE({1;1;1;1;1;1;1;1;1}) // returns {1,1,1,1,1,1,1,1,1}

and the result is handed off to MMULT for the second array as before.

Dynamic Array Formulas are available in Excel 365 only.
Dave Bruns

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.