The SUM function in Google Sheets is one of the most widely used functions because it is so simple and useful. The SUM function, as the name implies, adds together numerical values and returns the sum. Continue reading to learn about the SUM function.
The SUM function in Google Sheets only requires one argument (function inputs that appear between the parenthesis separated by commas), but can handle any number of additional arguments.
=SUM(value_1, [value_2, ...])The SUM function returns the sum of numerical values, including numbers and references to cells which contain numbers. For example, to find the sum of 4 and 6, you could simply write the following formula:
=SUM(4, 6)Notice how the arguments, or function inputs, are within the parenthesis and separated by commas. The SUM function would take these two arguments and return the sum, which is 10. The SUM function can also take more arguments, so you could see a SUM function in Google Sheets with any number of arguments.
The SUM function can also handle cell references. In order to find the sum of the numbers contained in cells A1, B2, and C3, you could write the following formula:
=SUM(A1, B2, C3)This formula would return the sum of any values contained in those three cells.
The SUM function can also take a range of cells as its argument. A range of cells in Google Sheets is written as the first cell and the last cell in the range separated by a colon. For example, to find the sum of all values contained in cells A1, A2, A3, A4, and A5, you could write the following formula:
=SUM(A1:A5)The argument A1:A5 tells Google Sheets "every cell from A1 to A5."
Blank cells and cells which contain non-numerical data (such as text) can also be included as arguments, but the SUM function will ignore them and treat them as zeros.
The SUM function can also handle different types of arguments mixed together, so you might see a formula like the following:
=SUM(A1, 5, C1:C10)This formula takes a number, a single cell reference, and a cell range reference, and would return the sum of all the arguments.
Below are some examples of the SUM function in action.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month 1 Revenue | Month 2 Revenue | Month 3 Revenue | |
| 2 | Product 1 | 100 | 200 | 150 |
| 3 | Product 2 | 50 | 150 | 200 |
| 4 | Product 3 | 10 | 300 | 400 |
| 5 | Product 4 | 50 | 250 | 500 |
In the table above we have the hypothetical revenues of four products over the course of three months. Say we're interested in finding the total revenue (for all products) for month 1. We could type the following formula:
=SUM(B2:B5)This formula would add the contents of every cell from B2 to B5, and would return a value of 210.
Now say we're interested in finding the total revenue only for product 1 for all three months. We could use the following formula:
=SUM(B2:D2)This formula would return the sum of Product 1 revenues for all three months which are contained in cells B2 to D2. This would return a value of 450.
Now say that we want to find the total sum of all four products across all three months. The SUM function in Google Sheets can handle what's known as a 2-dimensional range of cells, which is a range that spans multiple rows and columns. For example, we could use the range B2:D5 in the following formula:
=SUM(B2:D5)This formula would find the sum of all the cells from B2 to D5, which would return 2,360 which is the total revenue for all four products across all three months.
Say we know that in the future we will be adding more products to the table and don't want to update our formula each time we want the Month 1 revenue. For example, if we added Product 5, we would need to update our total Month 1 Revenue formula to =SUM(B2:B6).
To avoid this, Google Sheets lets us sum entire rows and columns. To get the sum of all of column B, we would use the argument B:B.
=SUM(B:B)This formula would return the sum of every value in column B. The text in cell B1 will be ignored, as will all empty cells, so we're free to add more products to the table and know that the Month 1 Revenue for each product will be included in the sum.
Now say we know we want to add more months, and don't want to update our Product 1 revenue formula each time we add a month. We can find the sum of all of row 2 with the argument 2:2.
=SUM(2:2)This formula would return the sum of all values in row 2, so we could add more months to the table and know that we're always getting the total revenue for Product 1.
As with all functions in Google Sheets, the best way to learn and commit them to memory is to complete some hands-on practice problems. Try some practice problems with the SUM function now!