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?

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

### Syntax

`=TOTALCOL(range)`

### Arguments

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

### Example

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.**

**Formula:**

`=TOTALCOL(sales)`

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:

`=MMULT(TRANSPOSE(ROW(Sales)^0),--Sales)`

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.

`=TRANSPOSE(ROW(data)^0)`

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.

**Formula:**

`=MMULT(SEQUENCE(1,ROWS(Sales),1,0),--Sales)`

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

`=SEQUENCE(1,ROWS(Sales),1,0)`