Sum a Column in Google Sheets: Formulas and Tips

To sum a column in Google Sheets, enter =SUM(B2:B10) in an empty cell, replacing the range with the cells you want to add, then press Enter. For a column with a heading in row 1, you can also use =SUM(B2:B) to include its current and future entries.

Use SUM for a straightforward column total

The SUM function adds numbers and returns one total. In a formula such as =SUM(B2:B10), the colon means “through,” so Sheets includes every cell from B2 to B10, including the endpoints. Put the formula outside the range being added—such as in B11—or in another column to keep the result separate from its inputs.

For example, suppose B2:B6 contains 12, 18, 25, 30, and 40. With those values in B2:B6, =SUM(B2:B6) returns 125. You can type the formula directly into a cell or select a cell, type an equals sign, and enter the function and range. Press Enter to calculate the result.

Google Sheets recalculates the total when values in the referenced cells change. That makes a formula more useful than typing a total manually: the result stays connected to the source data and updates as you correct or replace amounts.

Choose a range that fits your sheet

A fixed range is a good choice when the list has a known end. For example, if monthly expenses occupy B2:B20, =SUM(B2:B20) clearly limits the calculation to those rows. It also makes it easier to see which data the total covers.

If records will be added later, use an open-ended range. Assuming B1 is a heading and amounts begin in B2, =SUM(B2:B) includes all cells from B2 to the bottom of column B. This avoids updating the formula every time you append a row. It does, however, include any other numeric values placed farther down the same column, so keep unrelated figures out of that column.

You can sum a whole column with =SUM(B:B). This is convenient when the column contains only the values you intend to total, but it includes the header cell and every other cell in B. Text headings are not added, but numeric notes or a second total lower down can unexpectedly affect the result. A whole-column reference also makes the formula less explicit than a carefully chosen data range.

For totals across separate blocks, put each range inside the same SUM function, separated by commas. If January figures are in B2:B5 and March figures are in D2:D5, =SUM(B2:B5,D2:D5) adds both blocks and skips column C. In some spreadsheet locales, function arguments use semicolons instead of commas; if a formula with commas is rejected, check the spreadsheet’s locale settings.

Enter the formula with the Sheets interface

The quickest approach is usually to select an empty cell and type the formula. You can also use the function menu: select the result cell, choose Insert > Function, then choose SUM if it appears in the function list. After Sheets inserts the function, provide the range between its parentheses and confirm the formula.

To avoid mistyping a range, start the formula in the destination cell, type =SUM(, and use the pointer to select the cells you want to add. Sheets inserts the selected range into the formula. Finish with a closing parenthesis if needed, then press Enter. The formula bar above the grid lets you inspect or edit the expression later.

Keep the result cell outside the cells being summed. If a formula in B2:B10 tries to include itself—for example, a total placed in B10 with =SUM(B2:B10)—the calculation creates a circular dependency. Put the total in B11 or in another column, or adjust the range so it excludes the formula cell.

Sum only rows that meet a condition

When a column contains several kinds of entries, SUMIF adds values only when a corresponding cell matches one condition. Assume categories are in A2:A8 and amounts are in B2:B8. With Travel amounts of 40 and 65 among the rows, =SUMIF(A2:A8,"Travel",B2:B8) returns 105. The first range is checked for the criterion, and the last range supplies the values to add.

For a condition that refers to a cell, place the criterion in a cell rather than typing it into the formula. If the category to total is written in D1, then with categories in A2:A8 and amounts in B2:B8, =SUMIF(A2:A8,D1,B2:B8) uses whatever category D1 contains. That is helpful for a reusable summary where you can change the selection without editing the formula.

Use SUMIFS when more than one condition must be true. For instance, if A2:A20 contains departments, B2:B20 contains months, and C2:C20 contains amounts, =SUMIFS(C2:C20,A2:A20,"Sales",B2:B20,"January") adds amounts for rows where the department is Sales and the month is January. SUMIFS places the sum range first, followed by condition-range and criterion pairs. The ranges should have matching dimensions.

Get a total for filtered data

A SUM formula normally adds every numeric value in its selected range, including rows hidden by a filter. If you want a subtotal that responds to filtering, use SUBTOTAL instead. Assuming the amounts are in B2:B20, =SUBTOTAL(9,B2:B20) calculates a sum for the range and excludes rows removed from view by a filter.

In this function, 9 selects the SUM operation. SUBTOTAL is useful for a filtered list when the displayed records—not the entire underlying list—are what you want to total. Applying a filter does not change the values; it changes which rows are displayed, and the subtotal is designed to reflect the filtered view. By contrast, ordinary SUM remains a total of the full range.

This distinction matters when someone reads the result. Label the cell clearly, such as “Visible rows total,” if it changes with a filter. For an all-records total, use SUM and make that meaning clear. SUBTOTAL has options for other calculations too, but the number 9 specifically requests a sum.

Understand what is and is not added

SUM adds numeric values in its referenced cells. Blank cells and ordinary text, such as a column heading, do not contribute to the total. That behavior can make a formula appear to work even when a supposed amount is actually text: the entry is simply left out, which may produce a total that is too low.

Numbers stored as text are a common cause. They can arrive through an import, include an apostrophe, or contain formatting characters that prevent Sheets from recognizing them as numbers. If an amount is unexpectedly ignored, select the cell and check whether its contents behave like a number; changing the display format alone may not convert text into a numeric value. Correct the underlying entry or convert the imported data before relying on the sum.

Errors in referenced cells are different from text: an error such as #VALUE! can cause the total formula to show an error rather than a number. Find and correct the error in the source range. Also remember that dates and times are stored as numeric values in spreadsheets. Adding a range that contains dates can therefore produce a numeric total, but that result usually has no useful meaning as a date summary.

Formatting affects how the result looks, not what SUM calculates. If the formula returns 125 but displays as 125.00, the underlying total is still 125. Use the toolbar’s number-format controls to show currency, a chosen number of decimal places, or another appropriate display. Choose a format that matches the data rather than treating formatting as a way to fix incorrect inputs.

Check the result and avoid common mistakes

If a total looks wrong, check the range before changing the formula. A range that stops one row too soon omits data; one that extends too far can include a note or a previous total. Click the formula cell and inspect the colored range highlight in the grid, or read the range in the formula bar.

Confirm that every intended amount falls inside the referenced range.
Look for numbers stored as text, blanks where amounts should be, and formula errors.
Check whether a filter is active and whether you need SUM or SUBTOTAL.
Make sure the total cell is not included in its own range.
Verify that another numeric value lower in an open-ended or whole-column range is not being added by accident.

For a quick independent check, select the numeric cells and look at the summary shown near the lower-right area of the Sheets window, if that calculation is visible. The displayed summary can help spot a mismatch, but it is not a substitute for checking the formula’s exact range and should not replace a saved formula when you need a persistent total.

Google Sheets and Excel: the basic formula is similar

The basic SUM syntax is shared by Google Sheets and Microsoft Excel: both use formulas such as =SUM(B2:B10). A workbook or spreadsheet using this ordinary formula is generally easy to understand in either program. The interface, menus, and some advanced functions differ, so instructions for one application should not automatically be followed in the other.

In Google Sheets, use its browser-based grid and menus, including Insert > Function for function insertion. Excel has its own ribbon and AutoSum tools. For a simple column total, there is usually no need to choose a different formula just because the file moves between Sheets and Excel. For more complex formulas, verify that the function and behavior are supported by the destination application.

Frequently Asked Questions

How do I sum an entire column in Google Sheets?

Enter =SUM(B:B) in an empty cell to add numeric values throughout column B. If row 1 is a heading and entries begin in row 2, =SUM(B2:B) avoids including the heading cell and is a clearer choice for a growing list.

How do I add a column without including the header?

Start the range below the header. If the heading is in B1 and the values are in B2 through B25, enter =SUM(B2:B25). For values that will continue to be appended, use =SUM(B2:B).

Why is my Google Sheets SUM formula returning zero?

The selected range may be empty, or its entries may be stored as text rather than numbers. Check that the formula points to the correct cells and that the amounts are recognized as numeric values. A display format by itself does not necessarily convert text into numbers.

How do I sum only visible rows after filtering?

Use SUBTOTAL with function number 9. If the values are in B2:B20, =SUBTOTAL(9,B2:B20) sums the rows left visible by the filter. A regular SUM formula includes filtered-out rows in its total.

Can I sum two separate ranges in one formula?

Yes. Put both ranges inside SUM and separate them with a comma, such as =SUM(B2:B5,D2:D5). If your Sheets locale expects semicolons between function arguments, use the separator required by that locale.

Does SUM update when I change a value?

Yes. A SUM formula recalculates when a cell in its referenced range changes. If you add records outside a fixed range, edit the formula to include them or use an open-ended range such as B2:B.

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.