How to Sum Multiple Columns in Excel

To sum multiple columns in Excel, you don't need one formula per column. A single SUM can take a range that spans several columns, like =SUM(B2:D5), or several ranges separated by commas. This guide shows every common case on one small sales sheet: a grand total, totals per row and per column, columns that aren't side by side, and totals that only count some rows.

The sample data#

All the examples use these monthly sales figures in A1:D5:

SUM ignores text and empty cells, so the names in column A never get in the way. The SUM function guide has the full syntax if you need a refresher.

How to sum multiple columns with one formula#

Give SUM a range that covers all the columns you want. The range runs from the top-left cell to the bottom-right cell:

=SUM(B2:D5)

The result is 1,540: every number in Jan, Feb and Mar added together. Excel treats B2:D5 as one block of 12 cells, three columns wide and four rows tall.

You can type the range or select it with the mouse after typing =SUM(. To add a whole column without row numbers, write =SUM(B:D). That picks up new rows automatically, but don't put the formula inside columns B to D, or Excel warns about a circular reference (a formula that includes its own cell).

How to sum each row across columns#

To get a total for each rep, sum across the row and fill the formula down. In E2, type:

=SUM(B2:D2)

Ana's total is 400. Select E2:E5 and fill down (Windows: Ctrl+D; Mac: ⌘D). Because the references are relative, row 3 becomes =SUM(B3:D3) and so on. Ben gets 300, Chloe 590 and Dev 250.

How to sum each column#

To total each month, sum down the column and fill the formula to the right. In B6, type =SUM(B2:B5) to get 485 for Jan. Select B6:D6 and fill right (Windows: Ctrl+R; Mac: ⌘R). Feb gives 535 and Mar gives 520.

AutoSum is faster. Click the cell under a column and press Alt+= on Windows or ⌘+Shift+T on Mac. Excel guesses the range and you press Enter.

A useful trick: select B2:E6, which is the numbers plus one empty row and one empty column, then use AutoSum once. Excel fills in every row total, every column total and the grand total in one go.

How to sum columns that aren't next to each other#

List each range inside one SUM, separated by commas. To add Jan and Mar but skip Feb:

=SUM(B2:B5,D2:D5)

The result is 1,005 (485 + 520). SUM accepts up to 255 arguments, so you can keep adding ranges.

To select the ranges with the mouse, type =SUM(, drag over the first range, then hold Ctrl (Mac: ⌘) and drag over the second. Excel adds the comma for you.

Avoid =SUM(B2:B5+D2:D5). With a plus sign inside, Excel adds the two ranges cell by cell. That happens to work in Microsoft 365, but older versions can return a wrong total or #VALUE!, and any text cell in either range breaks it. Commas are the safe choice in every version.

How to sum multiple columns with a condition#

SUMIF can only add one column. If you give it a sum range several columns wide, such as =SUMIF(A2:A5,"Ben",B2:D5), Excel quietly uses just the first column and returns 90, not Ben's real total.

To add every month for one rep, use SUMPRODUCT:

=SUMPRODUCT((A2:A5="Ben")*B2:D5)

The result is 300. The test A2:A5="Ben" gives TRUE for Ben's row and FALSE for the others. Multiplying turns TRUE into 1 and FALSE into 0, so only Ben's numbers survive, and SUMPRODUCT adds them up.

In Excel 2021 or Microsoft 365 you can also write =SUM(FILTER(B2:D5,A2:A5="Ben")). The article on how to filter and sum in one formula covers that approach. If you need to pull several columns back for one match, rather than add them, see VLOOKUP with multiple columns.

How to show each row's share of the total#

When you divide each row by a grand total, lock the total with dollar signs. Put the grand total =SUM(B2:D5) in E7. Then in F2:

=E2/$E$7

Format it as a percentage. Ana's share is 26% (400 ÷ 1,540). When you fill down, E2 changes to E3, E4 and E5, but $E$7 stays fixed. Without the dollar signs, the next row would divide by E8, an empty cell, and show #DIV/0!. Press F4 (Mac: ⌘T) right after typing E7 to add the dollar signs.

Why is my SUM of multiple columns wrong?#

A wrong total usually comes from what is inside the range, not the formula. Check these:

1. Numbers stored as text. SUM skips them. They often sit on the left of the cell with a small green triangle. Convert them first.
2. A total row inside the range. If row 6 already holds totals, =SUM(B2:D6) counts them twice.
3. An error in one cell. One #N/A makes the whole SUM show #N/A. See how to sum while ignoring errors.
4. Hidden columns. SUM still adds hidden columns. Exclude them from the range if they shouldn't count.

The main SUM function article has more fixes, including AutoSum picking the wrong range.

Practise it#

Summing across and down is a skill you use every day at work and in nearly every Excel test. Practise SUM in a live spreadsheet: you write the totals yourself and the lesson checks them instantly. Then practise absolute references to lock a total with $ and fill a percentage column without errors.