The fastest way to total a column or row in Excel

The SUM function is the standard way to add numbers in Excel. Type =SUM(, select the cells you want to add, then close the parenthesis and press Enter. Excel will show the total in that cell.

If you have numbers in cells A1 through A10, you would type =SUM(A1:A10). The colon tells Excel to include every cell between the first and last one you named. You can also add individual cells that are not next to each other by typing =SUM(A1,A3,A5) — each cell separated by a comma.

Excel also offers a shortcut: select the cells you want to total, and look at the bottom right of your screen. The status bar shows the sum automatically, without you typing anything. This is useful for a quick check, but it does not put the total in a cell where you can use it in other calculations.

Key Takeaways

  • The SUM function adds a range of cells by typing =SUM(A1:A10) or adds individual cells by typing =SUM(A1,A3,A5).
  • You can total multiple separate ranges in one formula by typing =SUM(A1:A5,C1:C5) with commas between each range.
  • The status bar at the bottom of Excel shows a quick sum of any cells you select, without requiring a formula.
  • SUBTOTAL and AGGREGATE functions let you total only visible cells or skip cells with errors, which SUM does not do.

Adding ranges that are not next to each other

If the numbers you need to total are spread across different parts of your spreadsheet, you can add multiple ranges in one SUM formula. Type =SUM(A1:A5,C1:C5,E1:E5) to add three separate groups. Each range goes inside the parentheses, separated by a comma.

This method works whether the ranges are in the same row, the same column, or scattered across the sheet. Excel adds all the numbers in all the ranges you list and shows one total. This is faster than creating separate SUM formulas and adding those results together.

Totaling only visible cells with SUBTOTAL

When you filter a spreadsheet or hide rows, the SUM function still adds the hidden numbers — you just cannot see them. If you want to total only the cells that are currently visible on screen, use the SUBTOTAL function instead.

Type =SUBTOTAL(9,A1:A10). The number 9 tells Excel to sum only visible cells. (The number 109 does the same thing but also ignores cells you have manually hidden.) SUBTOTAL is especially useful in reports where you filter data by date, region, or category and need the total to change as you filter.

SUBTOTAL can do more than sum — the first number in the formula changes what it calculates. The number 3 counts cells, 4 finds the maximum, 5 finds the minimum, and 9 sums. A full list of these codes is available in Excel's help menu under "SUBTOTAL function".

Skipping cells with errors using AGGREGATE

The AGGREGATE function works like SUBTOTAL but also ignores cells that contain errors (like #DIV/0! or #N/A). This is useful when your data includes formulas that sometimes fail.

Type =AGGREGATE(9,6,A1:A10). The 9 means sum, and the 6 means ignore error values. Like SUBTOTAL, AGGREGATE also ignores hidden rows by default. If you have a spreadsheet where some calculations produce errors and you still need an accurate total of the rest, AGGREGATE prevents those errors from breaking your sum.

Creating a total row at the bottom of a table

If you have a table of data with numbers in a column, the easiest way to add a total row is to click anywhere in the table and then click the Design tab (on Mac, the Table Design tab). Look for a checkbox labeled "Total Row" and check it. Excel automatically adds a row at the bottom and fills it with SUM formulas for each column of numbers.

You can then click each cell in the total row to change what it calculates — for example, switching from SUM to AVERAGE or COUNT. This method is faster than typing formulas by hand, and Excel updates the totals automatically if you add or remove rows from the table.

Adding numbers across multiple sheets

To total cells from different sheets in the same workbook, type the sheet name before the cell reference. For example, =SUM(Sheet1!A1:A10,Sheet2!A1:A10) adds the range A1:A10 from both Sheet1 and Sheet2. The exclamation point separates the sheet name from the cell reference.

If your sheet name contains a space, put single quotes around it: =SUM('Sales Data'!A1:A10). You can add as many sheets as you need in one formula by separating each range with a comma. This is useful when you have data split across multiple sheets and need one master total.

Troubleshooting when your total seems wrong

If your SUM formula shows a result that does not match what you expected, the most common cause is that some cells contain text instead of numbers. Excel treats text as zero in calculations, so a cell that looks like a number but is actually stored as text will not be included in the sum.

Check whether the numbers in your range are aligned to the left (text) or the right (numbers) in their cells. If they are left-aligned, they are text. You can convert them by selecting the range, going to the Data tab, and clicking "Text to Columns", then clicking Finish. Another cause is that you selected the wrong range — double-click the formula to see which cells are highlighted in color, and verify that you included all the numbers you meant to.

Frequently Asked Questions

Can I use SUM on cells in different columns?

Yes. Type =SUM(A1:A10,C1:C10) to add all numbers in column A and column C. You can mix columns and rows in the same formula — just separate each range with a comma.

What is the difference between SUM and SUBTOTAL?

SUM adds all cells in a range, including hidden ones. SUBTOTAL adds only visible cells when rows are filtered or hidden. Use SUBTOTAL in reports where you filter data and need the total to reflect only what you see on screen.

How do I total a column without knowing how many rows there are?

Type =SUM(A:A) to sum the entire column A, or =SUM(A1:A1000) to sum a large range. Excel will add all numbers in that column or range, even if you add more rows later. The entire-column method is slower on very large spreadsheets.

Why does my SUM formula show zero when I know there are numbers?

The numbers are likely stored as text, not as actual numbers. Check whether they are aligned left (text) or right (numbers) in their cells. If they are left-aligned, select them, go to Data, click Text to Columns, and click Finish to convert them.

Can I total only cells that meet a certain condition?

Yes, use SUMIF or SUMIFS. Type =SUMIF(A1:A10,">100") to add only cells greater than 100, or =SUMIFS(A1:A10,B1:B10,"North") to add cells in column A only where column B says "North".