Google Sheets: How to Create a Sum Column Automatically

To total a column in Google Sheets, enter =SUM(B2:B20) in the cell where you want the result, changing the range to match your data. For example, if the numbers are in B2 through B20, put the formula in B21. Sheets adds the values and updates the total when they change. If by a sum column you mean a new column that adds values across each row, use a formula such as =SUM(B2:D2) in the first result row and fill it down.

This guide covers both common meanings: a total below a column of numbers and a separate column of row totals. The instructions are for Google Sheets in a web browser. The SUM formulas also work in Excel, although menu layouts and some shortcuts differ.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
A range of cells containing numbers, such as a list of expenses in B2:B20 or several values in B2:D20.
An empty cell for the total or result column. Avoid putting a total formula inside the range it is meant to add.

Step-by-Step

Identify what you want to add. Look at the layout before entering a formula. If one column contains values to combine into one grand total, you need a total cell, usually just below the numbers. If each row has several values and you want a sum beside each row, you need a new sum column. For instance, a sales sheet might have a single Amount column for one overall total, or separate columns for January, February, and March that need a total for every product.
Check that the values are usable numbers. Click a few cells in the data range and look at their contents in the formula bar. Values may look like numbers but be stored as text, especially if they were pasted from another source or include an apostrophe. SUM ignores text in a range, so check this before assuming the formula is wrong. Keep headings outside the range; for example, if B1 says Amount and the first value is in B2, start the sum at B2.
Choose a destination cell for a single total. For a simple list in B2:B20, select B21, directly below the last value. You can put the total elsewhere, such as in a summary area, as long as it is not within B2:B20. Add a label like Total in A21 if that helps readers understand the result. Keep the label outside the numeric range being summed.
Enter the SUM formula. With values in B2:B20 and the result intended for B21, type =SUM(B2:B20) in B21 and press Enter. The formula includes the first and last cells in the range. For example, if B2:B5 contains 12, 8, 15, and 5, the formula =SUM(B2:B5) returns 40. The colon means include every cell from the starting cell through the ending cell.
Adjust the range to match your sheet. If the data is in column C from row 3 through row 14, put =SUM(C3:C14) in a cell outside that range. The column letter and row numbers must match the data. For separated cells rather than one continuous range, you can list them as individual arguments; with values in B2, B4, and B7, use =SUM(B2,B4,B7). For most ordinary lists, a continuous range is easier to review and maintain.
Use an open-ended range when the list will grow. If values begin in B2 and may continue down column B, place =SUM(B2:B) in a different column, such as D2. This sums from B2 to the bottom of column B, including numbers added later. Do not place that formula in column B: it would include its own cell and create a circular reference. An open-ended range is convenient for an expanding list, while a fixed range is useful when you want to limit the total to a known set of rows.
Create a row-by-row sum column if that is what you need. Suppose the values for each item are in B2:D20 and you want each row’s total in E. Enter a heading such as Row Total in E1. With the first row’s values in B2:D2, put =SUM(B2:D2) in E2 and press Enter. The result adds the values across that row, rather than adding the whole table into one grand total.
Fill the row formula down. Click E2, then drag the small blue square at the lower-right corner of the selected cell down through E20. Google Sheets adjusts the relative references for each row: E3 will add B3:D3, E4 will add B4:D4, and so on. You can also select E2:E20 after entering the formula in E2 and use Ctrl+D on Windows or ChromeOS, or Command+D on a Mac, to fill down. Check the last few formulas to confirm the row numbers changed as intended.
Format the result for easy reading. Select the total cell or sum column and use the toolbar options for currency, percentage, or number formatting as appropriate. You can also use Format > Number and choose a suitable format. Formatting changes how a value appears, not the underlying calculation. For example, a currency format displays a money symbol and decimal places but does not change the amount being summed.
Test that the formula updates. Temporarily change one value in the source range and see whether the total changes by the same amount. Undo the test with Ctrl+Z on Windows or ChromeOS, or Command+Z on a Mac. If you use a fixed range and add new rows below its final cell, remember to expand the range or switch to an open-ended range. A formula does not automatically include data placed outside its references.

Common Mistakes

Including the formula cell in its own range. A formula in B21 that sums B2:B21 includes B21 itself. This can produce a circular dependency warning instead of a result. Use B2:B20 for a total in B21, or put the result in a separate area. With an open-ended range, keep the formula in a different column from the source data.

Starting or ending one row too early. A range such as B2:B19 leaves out B20, while B1:B20 may include a heading in B1. Check the first and last data rows before pressing Enter. Clicking the displayed range in the formula is a quick way to inspect which cells are selected.

Expecting SUM to include text that looks numeric. SUM adds numeric values, not arbitrary text. A pasted entry such as a number with a leading apostrophe can be treated as text and ignored. Correct the source cell so it contains a number, rather than trying to disguise the issue with a different total formula.

Copying a row formula without checking its references. When you fill a formula down, normal references change by row. That is usually right for a row total, but it may not be right if a formula needs to refer to one fixed cell. Review a few filled cells before relying on the results, especially when the formula is part of a larger sheet.

Typing a total inside the data table without planning for it. A grand total can be mistaken for another data value, especially if it sits directly below a list and later gets included in another calculation. Label the total clearly and make sure other formulas use only the rows containing source data.

Tips

Use a clear label. Put Total or Grand Total beside a single-column result, and use a heading such as Row Total above a calculated sum column. Labels make it easier for someone else to distinguish source values from formulas.
Keep source data and summaries separate. A summary block to the right of the table can be easier to maintain than adding totals among the data. It also reduces the chance of including a total in a later sum by mistake.
Choose a fixed or expanding range deliberately. A range like B2:B20 is easy to audit and excludes unrelated cells. A range like B2:B is useful when new entries will be added regularly. In either case, check whether blank rows or other numbers in the selected range could affect your result.
Use SUM for clarity. A formula such as =SUM(B2:B20) makes the intended calculation easy to recognize. It is also convenient to edit if the data range changes, rather than manually adding many cell references.
Make formulas easier to scan. Apply consistent number formatting to the source data and result. If values represent money, choose a currency format; if they represent counts, use a plain number format. Avoid changing the source values just to make them display differently.
Remember that the formula and the displayed value are different. Clicking a result cell shows the formula in the formula bar. This helps you verify the references even when the cell itself displays only the calculated number.

Troubleshooting

The result is 0 or too small. First check that the formula refers to the intended cells. Then inspect any values that were pasted or imported; text-formatted numbers may not be included. Click a questionable source cell and look for an apostrophe or unexpected spaces in its contents. Correct the source data, then check whether the total updates.

The cell displays an error about a circular dependency. The formula may include its own destination cell, directly or through another formula. Move the total outside the range or shorten the end of the range so the formula cell is excluded. If other formulas refer back to the total, trace those references too.

The formula appears as text instead of calculating. Confirm that it begins with an equals sign and that the cell does not contain a leading apostrophe. If the cell is formatted as plain text, change it using Format > Number > Automatic, then edit the cell and press Enter. Re-entering the formula after changing the format can make Sheets evaluate it.

New entries are missing from the total. A fixed range stops at its specified last row. Expand the reference, for example from B2:B20 to B2:B21, or use an open-ended range such as B2:B with the result in a different column. Also make sure new entries were added in the column the formula actually references.

A sum column repeats the same result or adds the wrong cells. Check the formula in each row. The first result should refer to that row’s inputs, such as B2:D2 in E2. If you used dollar signs to lock references, remove them where row references should change as the formula is filled down. For a standard row-by-row total, ordinary references adjust automatically.

The total is right but looks wrong. The cell may have an unsuitable number format or too few displayed decimal places. Select the cell and choose an appropriate option from Format > Number. The underlying value may be correct even if the display rounds it.

Frequently Asked Questions

How do I sum an entire column in Google Sheets?
To sum values starting in B2, enter =SUM(B2:B) in a cell outside column B. Starting at row 2 keeps a heading in B1 out of the calculation. An open-ended range includes future numbers entered below the current data.

Can I put the total at the bottom of the same column?
Yes. Put the formula immediately below the final data row and end the range at the row above it. For example, if values are in B2:B20, enter =SUM(B2:B20) in B21. Do not include B21 in the formula range.

How do I add numbers across each row in a new column?
Enter =SUM(B2:D2) in E2 when that row’s values are in B2:D2. Fill the formula down the new column so each result uses the values on its own row.

Does SUM ignore blank cells?
Yes. Blank cells in the selected range do not add anything to the total. Text is also not added as a number, so a cell that looks numeric but is stored as text may be skipped.

Will the formula update when I change a value?
Yes. Google Sheets recalculates the result when a referenced value changes. If you add a new value outside a fixed range, however, you must expand the range or use a reference that includes the new row.

Is the SUM formula different in Excel?
No. The examples using SUM work in both Google Sheets and Excel. The browser menus and some keyboard shortcuts differ between the programs, so follow the instructions for the application you are using.

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.