Google Sheets: How to Sum a Column

To sum a column in Google Sheets, click an empty cell and enter =SUM(A:A), replacing A with the column’s letter, then press Enter. To add only a specific range, use a formula such as =SUM(A2:A20); Google Sheets adds the numeric values in that range and ignores text and blank cells.

What the SUM formula does

SUM adds numbers from individual cells, ranges, or a combination of both. The formula starts with an equals sign, followed by the function name and the cells in parentheses. A colon between two cell references means “through,” so A2:A20 refers to every cell from A2 to A20 in column A.

For example, suppose a worksheet has daily sales amounts in B2:B6: 12, 18, 7, 23, and 10. With those values in B2:B6, enter =SUM(B2:B6) in an empty cell; the result is 70. The result updates when a number in the range changes, so you do not need to add the figures again by hand.

SUM adds numeric values, not the visual appearance of cells. A cell formatted to look like currency is still a number, so it can be included. A label such as “Total” is text and is ignored. Dates are stored as numbers in spreadsheets, so a date included in a range can also affect the total; avoid including date columns in a sum of amounts.

Sum a selected range instead of the whole column

A bounded range is often the clearest choice when a list has a known start and end. If expenses are in C2:C50, place the formula in a separate total cell and use =SUM(C2:C50). This includes the cells from row 2 through row 50, but not a new expense entered in row 51. Expand the range when the list grows, or use an open-ended range if you want new entries below the current data to be included automatically.

To add cells that are not next to each other, list each reference with commas. With amounts in B2, B5, and B9, enter =SUM(B2,B5,B9) in a different cell. If the relevant values are in separate blocks, you can include multiple ranges in one function, for example =SUM(B2:B10,D2:D10). In a Google Sheets locale that uses semicolons as formula separators, the separators may need to be semicolons instead.

Use a range that matches the data, rather than selecting an entire sheet or unrelated columns. A narrow range makes it easier to check which values are included and lowers the chance of accidentally adding a date, subtotal, or other number that does not belong in the calculation.

Sum an entire column safely

An entire-column reference is convenient when rows are added regularly. If the values are in column D, the formula =SUM(D:D) includes numeric entries throughout that column, including future entries. It normally ignores a text heading in D1, which is why whole-column sums are often used for lists with a header row.

Do not put that formula in column D itself. If a formula in D20 sums D:D, it includes its own result cell, creating a circular reference. Put the total in another column or on a summary sheet instead. If your table has a total row in the same column, a whole-column sum can also count that total again. In that case, use a range that stops before the total row, or keep the summary outside the source column.

An open-ended range can be a useful middle ground. With amounts beginning in D2, =SUM(D2:D) includes row 2 and everything below it without including a heading in D1. It still needs to be placed outside column D, and you should check that no unrelated numbers farther down the column will be picked up.

Enter the formula using Google Sheets menus

You can type a formula directly, or insert the function from the Google Sheets menu in a web browser:

Select the empty cell where the total should appear.
Choose Insert > Function > SUM. Sheets inserts the SUM function for editing.
Select or type the range inside the parentheses. For example, if the values are in A2:A20, use =SUM(A2:A20).
Press Enter to calculate the result.

Check the formula bar or the selected cells if the result looks unexpected. The cell reference in the formula tells you exactly what is being added. If you copy a formula to another location, Sheets may adjust relative cell references automatically; inspect the updated range before relying on the copied total.

For a quick check without inserting a permanent total, select the numeric cells. Google Sheets can show a summary such as Sum in the status area at the bottom of the window when a range is selected. This is handy for a one-time check, but it is not a cell formula and will not become part of the worksheet. The displayed summary may depend on what is selected and the available status-bar calculation.

Choose the right method for your data

For a straightforward total, SUM is the right tool. If only some rows should count, use a conditional sum rather than manually selecting scattered cells. For example, imagine A2:A6 contains East, West, East, North, East and B2:B6 contains 12, 8, 15, 5, 10. With regions in A2:A6 and amounts in B2:B6, enter =SUMIF(A2:A6,"East",B2:B6) in an empty cell. The result is 37 because it adds 12, 15, and 10, the amounts whose region is East.

SUMIF applies one condition. SUMIFS is useful when every row must meet multiple conditions, such as matching a region and a month. With regions in A2:A20, months in B2:B20, and amounts in C2:C20, a formula such as =SUMIFS(C2:C20,A2:A20,"East",B2:B20,"January") adds amounts only for rows where the region is East and the month is January. The result depends on the values in those cells. Keep the sum range and each criteria range the same size so rows line up correctly.

A normal SUM includes values in rows hidden by a filter. If the goal is a total that changes to reflect only the rows currently shown by a filter, SUBTOTAL is a better fit. With filtered amounts in B2:B100, enter =SUBTOTAL(9,B2:B100) in a cell outside the filtered data. The 9 tells SUBTOTAL to perform a sum, and filtered-out rows are excluded. This differs from a permanent total of all records, so choose according to whether the hidden rows should count.

Make the total easy to read

A sum can be correct even if it displays too many decimal places or lacks a currency symbol. Formatting changes how a value looks, not the arithmetic. Select the result cell and use the toolbar’s number-format controls, or choose Format > Number to select a format such as Currency or Number. The appropriate display depends on what the values represent; do not apply a currency format to counts or measurements just because the total is numeric.

You can also label the result, for example “Total expenses,” in a neighboring cell. Keep the label outside the range being summed, or make sure the formula excludes any numeric summary rows. A clearly labeled total is especially useful when other people will view or edit the sheet, since it distinguishes a calculated result from another data entry.

Common problems and how to fix them

The total is zero or too small: Some entries may be stored as text rather than numbers. Imported data, leading apostrophes, or inconsistent decimal and thousands separators can cause this. Check a few suspect cells and confirm their values are recognized as numbers; applying a number format alone may not convert text into numeric values.
The total is too large: Check whether the range includes a subtotal, an old total, dates, or unrelated numeric entries. A whole-column formula can include values far below the visible table.
The formula shows an error: Confirm that the cell references are valid and the parentheses are balanced. If the spreadsheet’s locale expects semicolons, replace comma separators in multi-argument formulas with the separator used by that locale.
The formula shows a circular dependency: The total cell is probably inside the range it is summing. Move the total outside that range or shorten the range so it does not include the formula cell.
The result does not change as expected: Verify that the edited cell is inside the referenced range and that the entry is numeric. Also check whether a filter or condition is intentionally excluding the row.

Formatting and data type are separate issues: making a text value look like a number does not necessarily make it usable in SUM. When numbers come from a CSV export or another system, inspect the original values before changing the formula. It is safer to correct the data than to hide a mismatch with a more complicated calculation.

Google Sheets and Excel: what differs

The basic SUM formula and cell-reference syntax are the same in Google Sheets and Microsoft Excel, so a simple formula can usually be copied between them. The menus differ. In Google Sheets, use Insert > Function > SUM or type the function in a cell. Excel for Microsoft 365 on Windows has an AutoSum command on the Home tab that can suggest a nearby range; Google Sheets does not use that Excel ribbon path. In either program, verify the suggested range before accepting it, especially if the data contains blank rows or an existing total.

Frequently Asked Questions

How do I add every number in a Google Sheets column?
Put the formula in a cell outside the data column. If the values are in column A, enter =SUM(A:A). It adds numeric values in the full column and ignores text and blank cells.

How do I sum a column while leaving out the heading?
If the heading is in A1 and values begin in A2, use =SUM(A2:A) in a cell outside column A. This includes new values added farther down the column while omitting the heading.

Can I sum only rows that match a category?
Yes. Use SUMIF for one condition or SUMIFS for multiple conditions. For instance, if categories are in A2:A20 and amounts in B2:B20, SUMIF can add only the amounts whose category matches the criterion you provide.

Does SUM include rows hidden by a filter?
Yes. SUM includes values in filtered-out rows. To total only rows left visible by a filter, use SUBTOTAL with function number 9 and the relevant range, such as =SUBTOTAL(9,B2:B100).

Why does SUM ignore a number that I can see?
The entry may be stored as text, perhaps because it was imported or has a leading apostrophe. Check whether Sheets recognizes the cell as numeric and correct the underlying value if needed; changing its display format alone may not fix it.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.