To get row totals of a range that contain numeric values, use the TOTALROW function or a custom formula with the MMULT function.
How to get row totals of a range?
- Type the =TOTALROW(range) formula in cell H3.
- Press Enter.
- The formula sums each row in the selected range.
The TOTALROW function uses a single, required argument, the range where we find the rows.
In the example, the data is in the range C3:G9. First, create a named range. Select the range, click the name box and type “Sales”. From now on, “Sales” refers to the range C3:G9.
The formula will return the sum of each row in the selected range.
Note: TOTALROW is a dynamic array function, so the result spills into a range, not a single cell. The function is compatible with all Excel versions. If you are working with a different version of Microsoft 365, press Ctrl+Shift+Enter to create the output array.
Get row totals with the MMULT function
Now let us see the workaround with the MMULT and row functions.
Generic formula to get row totals:
Apply the formula in cell H3:
Evaluate the formula from the inside out:
The ^0 operator raises each number in the array to the power of 0. When a number is raised to the power of 0, the result is always 1. After that, the formula creates an array the same size as the original.
The TRANSPOSE function transforms a horizontal array into a vertical array.
The MMULT function uses numeric values, so we need to use the double negative method to convert boolean values to 0s and 1s. For example, if the cell contains a number, the value is 1, else 0.
Finally, the MMULT function sums the values in each row of the selected range, resulting in an array of row totals.
Using the SEQUENCE function
=MMULT(--Sales, SEQUENCE(COLUMNS(Sales), 1, 1, 0))
You can use the SEQUENCE function to create an array with the same column size as the “Sales” range.
=SEQUENCE(COLUMNS(Sales), 1, 1, 0)
The SEQUENCE function uses four arguments:
- The no. of columns in the array.
- The no. of rows in the array: 1
- The starting value in the array: 1
- Step: 0
The double-negative method converts boolean values to 0s and 1s. Finally, the MMULT function multiples the “Sales” array and sums values in the “Sales” range.