What Is SUM Function In Google Sheets?
The SUM Google Sheets Function calculates the numeric values of the selected cells, that can be just row values, column values or a cell range, and returns the sum total.
With the SUM Function in Google Sheets, we can avoid the manual addition using the “+” arithmetic operator for the values one-by-one.
For example, we have a data of products and their units sold. We will find the total units sold using the Google Sheets SUM function.
Select cell B11, enter the formula =SUM(B2:B10) and press “Enter”, as shown below.
The output is “630” units sold.
Key Takeaways
- The SUM Google Sheets Function is a math function which returns the sum of all the selected values, regardless of the dates or any other variables in the dataset.
- The SUM function automatically ignores empty cells and finds the sum of only the numeric cell values.
- We can use the ARRAYFORMULA, FILTER, IF and other functions along with the SUM to get the required results.
- We can also use the AUTOSUM feature. The other SUM functions are SUM, SUMIF, SUMIFS, SUMPRODUCT, etc.
- An alternative instead of the ARRAYFORMULA is to execute the formula using the keys Ctrl+Shift+Enter.
Syntax
Thesyntax of the SUM Formula in Google Sheets is,
Thearguments of the SUM Formula in Google Sheets are,
- value1: It is the selected cell range to find the sum total. It is a mandatory argument.
- [value2, …]: It is the additional cell ranges that must be added along with value1. It is an optional argument.
How To Use SUM Function In Google Sheets?
We can use the SUM Function in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets Ribbon →
Step 1: Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Math” option right arrow → select the “SUM” function, as shown below.
Step 2: The “SUM” formulaappears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the Formula in the Worksheet Manually →
Step 1: Select an empty cell for the output.
Step 2: Type =SUM( in the cell, as shown below. [Alternatively, type =S or =SU and double-click the SUM from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some SUM Google Sheets Function examples and count the values that fulfils the set criteria.
Example #1 – Sum Cell Values in Google Sheets.
The dataset given below consists of five employees June and July months salary. Let us sum cell values in Google Sheets using the SUM function.
The steps to sum cell values in Google Sheets are,
Step 1: Select cell D2 and enter the formula =SUM(B2,C2), as shown below.
Step 2: Press “Enter”. Then, we will get the Google Sheets Autofill option, as shown below. We can either select it or use the fill handle option.
Step 3: Drag the formula from cell D2 to D6 using the fill handle, to get the following results.
The above formula, can be entered as =SUM(B2:C2), i.e., as a cell range, which is the best option for multiple cell values to avoid missing any values.
Example #2 – Sum Column Values in Google Sheets.
The dataset given below consists of three smartphone models, the units ordered on three particular dates. We will sum column values in Google Sheets using the SUM Google Sheets Function.
The procedure to sum column values in Google Sheets using the SUM is,
Select cell D2, enter the formula =SUM(C2,C3,C4,C5,C6,C7,C8,C9) and press “Enter”, as shown below.
The output is 2094. Since, there are multiple column values, the best way to enter the formula is as a cell range as =SUM(C2:C9).
Example #3 – Combine the SUM and ARRAYFORMULA Functions.
Consider the dataset that consists of employee details, such as their names, ID’s, department, their salaries from January to June, etc. Let us combine the SUM and ARRAYFORMULA Functions and calculate the values.
The procedure to combine the SUM and ARRAYFORMULA functions is,
Select cell C13, enter the formula =ARRAYFORMULA(SUM(E2:J11)) and press “Enter”, as shown below.
We notice that the total sum with or without the ArrayFormula is the same when the entire cell range is selected. Let us consider the scenario where we add the column ranges month-wise, individually.
So, select cell F13, enter the formula as =SUM(E2:E11+F2:F11+G2:G11+H2:H11+I2:I11+J2:J11) and press “Enter”. We will get a value error, as shown below.
Now, enter the same formula with the ArrayFormula, as shown below. Then, the formula will be, =ARRAYFORMULA(SUM(E2:E11+F2:F11+G2:G11+H2:H11+I2:I11+J2:J11))
Example #4 – Combine the SUM and FILTER Functions.
Consider the dataset with the 2022, 2023 and the 2024 sales of 3 products. Let us combine the SUM and FILTER Functions.
The steps to combine the SUM and FILTER Functions are,
Step 1: Select cell E2, enter the formula =SUM(FILTER(B2:D2,B2:D2>1400) and press “Enter”, as shown below.
Step 2: Drag the formula from cell E2 to E10 using the fill handle, to get the following results.
Important Things To Note
- Whenever we add a result cell or a helper table, ensure to leave some space between the dataset as a row or a column in-between. Let it not overlap the list.
- There is no need to insert direct cell values with double-quotes.
- We get the “#Value” error, if in some scenarios we do not execute the formula as an arrayformula.
- We get the “#NA” error, if the there is a mistake in the formula, indicating that the calculation cannot be performed.
- The SUM function automatically ignores empty cells and finds the sum of only the numeric cell values.
Frequently Asked Questions (FAQs)
A few reason the SUM Google Sheets Function isn’t working because of the following reasons:
a. The argument values or cell reference are not organized properly.
b. The cell range selected is modified or deleted.
c. We have inserted the result cells below the dataset and new data is added in the dataset
d. The cell value for the arguments given directly are inserted with double-quotes.
Here are a few ways to fix such problems:
1. Match the supplied cell range supplied with the dimensions of the source.
2. Always format the cell containing the output as a number.
3. Enter correct syntax formats.
4. Avoid extra spacing between the arguments of the formula.
5. Add values manually by default.
We often forget in which category a function falls, here, the “SUM” function. Then, we can insert the function as follows:
Choose an empty cell → select the “Insert” tab → click the “Function” option right arrow → click the “All” option right arrow → select the “SUM” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Also, we can use the same path to insert other SUM functions, such as SUMIF, SUMIFS, SUMPRODUCT, etc. as they are found just below the SUM function option as we can see in the above image.
Alternatively, we can find the Functions icon to insert the SUM Google Sheets Function by following the path shown below.
• Choose an empty cell → click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option → click the “All” option right arrow → select the “SUM” function, as shown below.
a. SUM calculates all the numeric values.
b. SUMIF calculates all the numeric values that fulfil a single criterion.
c. SUMIFS calculates all the numeric values that fulfills multiple criterias.
We can insert the Google Sheets FILTER function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Filter” option right arrow → select the “FILTER” function, as shown below.
We can insert the Google Sheets ARRAYFORMULA function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Google” option right arrow → select the “ARRAYFORMULA” function, as shown below.
[Note: This formula is found only in the Google Sheets and not in MS Excel.]
Download Template
This article must help understand SUM Google Sheets function with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is SUM Function in Google Sheets. We learn its syntax & how to use it with examples and a working template. You can learn more from the following articles.
Leave a Reply