Exceljet

Quick, clean, and to the point

Get row totals

Excel formula: Get row totals
Generic formula 
=MMULT(--rng,SEQUENCE(COLUMNS(rng),1,1,0))
Summary 

To get an array of row 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:

=MMULT(--data,SEQUENCE(COLUMNS(data),1,1,0))

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.

Explanation 

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:

=MMULT(--data

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:

SEQUENCE(COLUMNS(data),1,1,0) // returns {1;1;1;1;1;1;1}

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:

=MMULT(--data,{1;1;1;1;1;1;1})

returns the array:

{51;59;67;56;51;49;52;42;52}

These values are returned to cell K5, and spill into the range K5:K13.

Alternate syntax

In older versions of Excel that do not have the SEQUENCE, you may see an different syntax for this formula based on the TRANSPOSE function:

=MMULT(--data,TRANSPOSE(COLUMN(data)^0))

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 COLUMN function returns 7 numbers:

COLUMN(data) // returns {3,4,5,6,7,8,9}

And raising these to a power of zero with the exponent operator (^) returns 1 x 7 array of 1s:

COLUMN(data)^0) // returns {1,1,1,1,1,1,1}

Finally, TRANSPOSE flips the array to a 7 x 1 array of 1s:

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

and the result is handed off to MMULT as before.

Dynamic Array Formulas are available in Excel 365 only.
Author 
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.