Calculate a Column Sum in Google Sheets: Formulas

To add the values in a column in Google Sheets, use the SUM function. For example, if the numbers are in B2:B100, enter =SUM(B2:B100) in an empty cell; Google Sheets returns their total and updates it when the values change.

The quickest way to total a column

For a list with a heading in row 1 and numbers below it, a range that starts at row 2 is usually the clearest choice. If your values are in B2:B6, enter =SUM(B2:B6) in a separate empty cell. With values 12, 8, 15, a blank cell, and 5, the result is 40. The blank does not change the total.

To enter the formula:

Select the cell where you want the answer to appear. It can be below the list or elsewhere on the sheet.
Type the formula, adjusting the column letter and row numbers to match your data.
Press Enter. The cell displays the total, while the formula bar shows the formula when you select that cell.

You can also select the range and look at the bottom-right status area of Google Sheets for a quick sum. That is useful for a temporary check, but it is not a formula saved in a cell. Use SUM when you want a result that remains visible and recalculates as the source values change.

What SUM adds—and what it ignores

SUM adds numeric values in the cells or ranges you specify. It can handle whole numbers, decimals, and negative numbers. For instance, a refund recorded as -10 reduces a sales total by 10; it is not treated as a positive amount.

In a referenced range, blank cells and ordinary text such as a column heading are not added. This makes it practical to include a heading accidentally, although beginning the range beneath the heading is easier to understand. Do not assume that every value that looks like a number is stored as a number: data imported from another system can sometimes be text. If a value is not being counted, check its cell format and how it was entered.

A range uses a colon between its first and last cell. In B2:B100, both endpoints are included. A comma can separate multiple ranges or individual cells. If your data is in B2:B10 and D2:D10, the formula can add both ranges; this is helpful when the relevant values are separated by another column.

Choose a range or sum the entire column

A fixed range is often best when the list has a known size. With amounts in B2:B100, =SUM(B2:B100) is specific and easy for another sheet user to inspect. However, values entered in B101 later will not be included unless you extend the range.

For a list that will keep growing, you can refer to the whole column. If the total is placed in D1 and the entries are in column B, enter =SUM(B:B) in D1. This includes existing and future numeric entries anywhere in B. It also means that an unrelated number farther down the column can be included, so use this method only when the column is dedicated to the values you want totaled.

A whole-column formula must not be placed inside the same column it sums. For example, putting =SUM(B:B) in B101 makes the formula include its own cell, causing a circular dependency. Put the result in another column or use a bounded range that ends above the result cell. A range with extra room, such as B2:B1000, can be a practical middle ground if the data is expected to grow but the column also contains other content.

Keep the total current as rows are added

SUM recalculates when a value in its referenced range changes, so you do not need to run the calculation again after each edit. The main decision is whether the formula’s range will include future rows. A whole-column reference automatically covers additions in that column; a fixed reference does not extend beyond its last row.

For example, if a monthly expense list occupies B2:B40, the formula =SUM(B2:B40) is sufficient until entries are added below row 40. You could instead use =SUM(B2:B200) to leave room for later entries. That approach still avoids summing the heading in B1 and keeps the intended data area explicit. If the sheet is shared, make sure collaborators know where to add entries so they do not put new amounts outside the selected range.

When a formula is copied to another location, relative references can shift. If you copy a formula one column to the right, references such as B2:B40 may change to C2:C40. For a single total that is unlikely to be copied, this is usually not a concern. If you are building a repeated layout, check the reference after copying rather than assuming it still points to the original column.

Sum values that meet a condition

Sometimes the desired answer is not the total of every number, but the total for one category, date, or status. Google Sheets provides SUMIF for one condition. For instance, if categories are in A2:A20 and amounts are in B2:B20, you can total only rows labeled Travel:

With category names in A2:A20 and corresponding amounts in B2:B20, enter =SUMIF(A2:A20,"Travel",B2:B20). If the Travel amounts are 40 and 60, the result is 100. The criteria range and sum range should align row by row so each category is matched to its own amount.

For two or more conditions, use SUMIFS. With departments in A2:A20, statuses in C2:C20, and amounts in B2:B20, enter =SUMIFS(B2:B20,A2:A20,"Travel",C2:C20,"Approved") to add amounts whose department is Travel and whose status is Approved. Here, the result depends on the actual rows meeting both conditions. The sum range comes first in SUMIFS, unlike the order used in SUMIF.

These functions are useful for a report that needs category totals without rearranging the source data. They do not replace a simple column sum: use SUM when every numeric value in the chosen range should count.

Get a total that responds to a filter

A regular SUM includes every numeric value in its range, even when a filter hides some rows from view. This can surprise people who filter a table to show one category and expect the visible total to change. For a total that excludes rows hidden by a filter, use SUBTOTAL with the sum function code 9.

With amounts in B2:B100, enter =SUBTOTAL(9,B2:B100) in a cell outside the filtered data. It returns the sum of the rows currently shown by the filter and recalculates as the filtered view changes. This is useful for temporarily viewing the total for a subset of records without writing a separate SUMIF for every category.

Keep the distinction in mind: SUM answers “What is the total in this range?” while SUBTOTAL can answer “What is the total for the rows currently visible after filtering?” If you need a permanent category total independent of someone’s filter settings, a condition-based function such as SUMIF or SUMIFS is generally more suitable.

Fix a total that looks wrong

If the displayed answer is not what you expect, check the range before changing the function. A range that starts one row too low omits the first value; one that ends too early misses later entries. A whole-column reference may include numbers outside the intended list. Selecting the formula cell and reviewing the highlighted range can help reveal these mistakes.

The result is zero or too small: Confirm that the entries are numeric rather than text, and check that the formula points to the correct column. Imported values may need to be converted or cleaned before they behave as numbers.
The result is too large: Look for an extra number in the selected range, such as a subtotal already calculated elsewhere. Adding a subtotal and its underlying entries together counts the same amounts twice.
The cell shows an error: Check for a misspelled function, mismatched parentheses, or a circular reference caused by putting the result inside the range being summed.
The result does not change as expected: Verify that the changed cell is inside the referenced range. If rows are filtered, remember that SUM still includes values in filtered-out rows.

Formatting can also confuse interpretation. A cell formatted as currency may display a currency symbol, but the format does not change the arithmetic. Likewise, changing the number of decimal places displayed does not necessarily remove the underlying decimal portion. If a total appears rounded, inspect the actual values and the cell’s number format before assuming the formula has rounded them.

Formula versus a quick visual total

For a one-time check, selecting numeric cells and reading the status area’s sum is quick. It does not create a result cell, though, and the displayed total is not a substitute for a documented calculation in a report. A formula is easier to reuse, reference in another calculation, and audit later.

Google Sheets may also offer a SUM choice through its Functions controls when a range is selected. The exact convenience offered can depend on the selected cells and interface. Typing the function directly is predictable: choose an empty cell, enter the range, and press Enter. Excel also uses the SUM function and the same basic range notation, but its menus and interface are not identical to Google Sheets. These examples are for Google Sheets in a web browser.

Choose the simplest approach for the task: a status-area sum for a quick glance, SUM for a saved total, SUMIF or SUMIFS for criteria, and SUBTOTAL when a filtered view should affect the result.

Frequently Asked Questions

How do I sum an entire column in Google Sheets?
Put the formula in a cell outside the column being totaled. If the numbers are in column B and the result is in D1, enter =SUM(B:B). This includes future numeric entries in B, but can also include unrelated numbers elsewhere in that column.

Can I add a column that has a heading and blank cells?
Yes. If the heading is in B1 and the values are in B2:B20, enter =SUM(B1:B20) or, more clearly, =SUM(B2:B20). SUM ignores the text heading and blank cells in the range.

Why does my SUM formula return zero?
Common causes include selecting the wrong range or having entries stored as text instead of numeric values. Check a few source cells and confirm that the formula includes them. If the data was imported, converting the text values to numbers may be necessary.

Does SUM include rows hidden by a filter?
Yes. SUM adds numeric values in its range whether or not a filter currently hides their rows. Use =SUBTOTAL(9,B2:B100) when you want the total to reflect the rows displayed by a filter.

Can I total only one category?
Yes. Use SUMIF with a category range, the category to match, and the amount range. For categories in A2:A20 and amounts in B2:B20, =SUMIF(A2:A20,"Travel",B2:B20) adds the amounts for Travel.

Does the formula update when I add a new row?
It updates when the new value falls inside the formula’s referenced range. A fixed range such as B2:B100 includes additions through row 100, while a whole-column reference includes numeric entries anywhere in that column. Check the range if a new entry is not reflected.

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.