The SUM function in Google Sheets is an easy-to-use and versatile tool to quickly calculate totals in your spreadsheet.
Instead of manually adding values from each cell, SUM effortlessly calculates totals across an entire column and row with a single formula, saving you time and energy.
In this tutorial, you’ll learn everything you need to know about SUM in Google Sheets, from the basics of using the function, to advanced techniques for handling complex data.
Let’s jump right in.
What is SUM in Google Sheets?
The SUM function is a built-in Google Sheets feature that adds numbers within a cell range.
The function can save time, help reduce errors, and simplify performing calculations. Other benefits include:
- Add multiple cells. The SUM function allows you to calculate the total of multiple cells in one formula, thereby minimizing the risk of potential errors when adding manually.
- Allows range selection. A Google Sheets SUM formula lets you select a range of cells to add up instead of individual cells. This is especially useful when summing up values in a large dataset.
- Formula flexibility. The SUM function’s flexibility allows you to use it in diverse ways, such as summing up single or multiple ranges of cells or cell references in one formula. Also, the function won’t break if you delete a row or column within the formula’s cell range. You can embed the SUM function across a wide gamut of Google Sheets formulas.
- Updates dynamically. When using the SUM function, your total sum will automatically update as you add or remove data from your range.
SUM Function Syntax
The SUM function’s syntax is:
SUM(value1, [value2, …])
Where:
- SUM is the function that adds the values within the parenthesis.
- value1 is the first range or number you want to add together.
- value2 is an optional number or range you wish the formula to add to value1. Place a comma between each value to tell the formula you want to add another value to the total.
How to Use the SUM Function in Google Sheets
Add cell values in a column
Before we move on, we’ll import a sample data set into Google Sheets to demonstrate our examples.
Let’s connect Shopify to Google Sheets with Coefficient to pull retail data into the current spreadsheet.
Coefficient connects to your company apps and systems and imports your business data into Google Sheets instantly. You can easily automate, schedule, and customize your data imports with Coefficient.
Now, onto the example. Let’s use the SUM function to calculate the total values in column D (Total Price column).
Start by selecting an empty cell where you want the SUM formula to return your results (cell F3).
Next, click the Function icon on the Google Sheets toolbar. Select SUM from the drop down function menu to input it into the cell.
Like the example below, you can type the formula and select the appropriate option to enter it in the cell.
You can select each cell individually until you include all the cells in the column in your formula. However, this method can be tedious and time-consuming, especially when adding numbers within a large dataset.
A better and more efficient option is to select the entire range to quickly add it to your formula.
Select your cell range (D3:D18) and add a closing parenthesis.
Your complete formula should be:
=SUM(D3:D18)
Press Enter, and the formula should return the total value of your column values.
There you have it! That’s how you use the SUM function in Google Sheets to calculate the total numbers in a column.
Add cell values in a row
To calculate the sum of numbers across a row, follow the same steps as adding cell values in a column in the above method.
The main difference is that instead of using the values within a column, you’ll include the cell range within a row.
For example, to get the total amount of values from each quarter, use the SUM function and select the range of cells in each row.
Your formula should look something like this:
=SUM(D3:G3)
Your results should instantly appear in cell H3 when you press Enter.
Drag the fill handle on cell H3 down to apply the formula to the rest of the rows and quickly get the total values.
Add Values in Non-Adjacent Cells
Suppose you want to calculate the sum in non-adjacent cells. You can use the SUM function to do this.
In our previous example, let’s say you only want to add the cells in the 1st and 3rd quarter columns.
Add the total values in non-adjacent cells (D3 and F3) with this SUM formula:
=SUM(D3,F3)
As you can see, you can quickly sum numbers in non-adjacent cells by specifying the values as your parameters.
Remember to use a comma between values to tell the formula you want to add numbers from a separate cell.
Press Enter and your SUM formula will return this result:
Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.
Click and drag the fill handle to the rest of the cells to apply the formula to the remaining values.
Below is another example of using the SUM function in non-adjacent cells. Let’s sum cells in D7, E4, F10, and G3.
Input =SUM( in the empty cell where you want the formula to return your results. Then select each cell and hold down Ctrl (or Cmd key in Mac).
Your complete formula will look like this:
The result of the formula is seen below:
Combine the SUM and FILTER Functions
In Google Sheets, you can use the SUM and FILTER functions to easily add numbers only when they meet a specific condition.
The FILTER function returns filtered versions of the given source range, fetching only columns or rows that meet the conditions you specify.
The combined SUM and FILTER functions use this basic syntax:
=SUM(FILTER(range, condition1, condition2, …)
- range is where the values you want to add are located
- condition1 and condition2 refer to the conditions you wish to apply to the formula
Let’s add the 1st to 4th quarter numbers in the fourth row but only include values above 200.
The formula below will calculate this:
=SUM(FILTER(D4:G4,D4:G4>200))
The range is D4:G4 and your condition is D4:G4>200
The resulting total should exclude the 102 value in cell G4 since it does not meet our specified condition.
The result should be 1,164 and not 1,266.
While this method is handy, you can only use it when calculating the total sum of values in a single column or row. This use case is also transferable to Microsoft Excel.
Adding values or cell ranges in adjacent cells results in formula errors. In this case, a better function to use is SUMIF function or SUMIFS function, or COUNTIF function.
While you’re at it, check out some of our other Google Sheets tutorials:
- The Ultimate Guide to VLOOKUP in Google Sheets
- How to Use the Google Sheets QUERY Function
- How to Use the ArrayFormula in Google Sheets
- Salesforce KPI Dashboards: Download Free Google Sheets Templates
Limitations of the SUM Function in Google Sheets
The SUM function in Google Sheets greatly simplifies adding numerical values in a range of cells. However, its functionality has several limitations:
- Limited to numerical values. The SUM function can only add numerical values in a range of cells. Non-numerical values, such as text, can cause the function to return an error or formatting issues.
- Limited to a single range. The SUM function can only calculate the total values in a single range of cells.
- Excludes values in hidden rows and columns. SUM formulas will not include values within hidden rows or columns in a range you want to sum.
- Error handling. If the cells within the range you want to add contain an error value, such as #REF!, #VALUE!, or #DIV/0!, the SUM function naturally returns an error value. Consider using IFERROR in Google Sheets to help you handle SUM formula errors better.
SUM in Google Sheets: Easily Add Your Cells
The Google Sheets SUM function is a powerful tool that can help you quickly calculate the sum of a range of numbers.
Learning the function will help improve your ability to manage your spreadsheet data and improve its quality—and save you the hassle of manually adding the numbers on your spreadsheet.
Also, if you want to simplify the process of getting your business data into Google Sheets for your calculations, analysis, and reporting, then try Coefficient now.
