Get column totals

To get column totals of a range that contain numeric values, use the TOTALCOL function or a custom formula that uses the MMULT function.

This tutorial will show various methods to get column totals from a selected range. First, we introduce the TOTALCOL function, the simplest way to return column totals. In the second part of the article, we will write formulas that use regular Excel functions.

How to get column totals of a range?

  1. Type the =TOTALCOL(C3:G9) formula in cell C10.
  2. Press Enter.
  3. The formula sums each column in the selected range.




The TOTALCOL function uses a single, required argument, the range where we find the columns.


In the example, the data is in the range C3:G9. Next, select the range of cells, click the name box, and add a descriptive name to a range, for example, “Sales”. From now on, “Sales” refers to the range C3:G9.

get column totals in Excel



The formula returns the sum of each column in the selected range.

Note: TOTALCOL is a dynamic array function, that is mean the result spills into a range. The function is compatible with Excel 2010 up to the latest Microsoft 365 versions. So if you are working with Excel 2010, 2013, 2016, or 2019, press Ctrl+Shift+Enter to create the result array.

Get column totals using the MMULT function

To get column totals from a range, you can use matrix multiplication. To do that, apply the MMULT function that uses two arrays as input and generates a return array with the following conditions: the output array has the same number of columns as the first array. It uses the same number of rows as the second.

The formula in C10:

workaround with the TRANSPOSE and MMULT functions

The MMULT function needs a numeric input, so we use a double negative (–) to fill the array with zero instead of empty cells.

In the example, array2 contains 7 rows; the formula needs array1, which includes 5 columns. The result should be a 1×7 array since the result spills into the range C10:G10.


As a result of the formula, the ROW function returns a 1×7 array that contains row numbers:

=ROW(Sales) = {3, 4, 5, 6, 7, 8, 9}
=ROW({3, 4, 5, 6, 7, 8, 9)} = {1, 1, 1, 1, 1, 1, 1}

TRANSPOSE converts the horizontal array to vertical, from 7×1 to 1×7; we will use the result as array1 for the MMULT function. Finally, the MMULT multiplicates the two arrays and gets column totals.

Using the SEQUENCE function

Another method to create an array input for the MMULT function is to use the SEQUENCE function.


SEQUENCE example to get column totals

In this case, you do not need to use the TRANSPOSE function; the formula returns the column totals.


Additional resources

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.