In this article, you will learn how to use the SUM formula in Excel.
In Microsoft Excel, the SUM function adds together a range of cells.
The SUM formula in Excel is beneficial in various situations when you need to sum up a range of values. Examples include:
The syntax for the function is as follows:
Each of the "number" arguments can be a number, such as “10”, a cell, such as “A1”, a range of cells, such as "A1:A10", or a named range, such as "sales". You can enter up to 255 arguments by dividing each parameter by commas. The function will add all values within the specified range and return the sum.
As described above, you can use the manual inputs to use the SUM function, such as below.
Instead, you can use cell references to enter the argument(s) in the SUM formula. For example, if cells B4, C4, and D4 contain the values 1, 2, and 3, respectively, the following formula will return the value 6.
Also, the following formulas return the same value as shown in the screenshot below.
Alternatively, you can also use the AutoSum function to aggregate a range of cells.
In this section, you can learn essential points you need to bear in mind when using the SUM function in Excel. Most of these tips can be applied to other formulas.
Manual Input vs. Cell Reference
As we explained above, you can manually input numeric values in the formula to add them up. However, you should refrain from doing so when you need to calculate numbers with long digits or many decimal places, as there is a possibility of a typo(s) leading to a wrong calculation result. It is recommended to input those numbers into cells (manually), making it easier for you to double-check the values and then sum them up with the SUM function by cell reference.
#VALUE! error value
Look at the following screenshot. As you can see, if you conduct addition without the SUM function (Pattern 1), when one of the added cells contains a non-numeric value (text), the formula returns #VALUE! Error. On the other hand, if you use the SUM formula (Pattern 2 and 3) and aggregate the same range of cells, the formula returns the total of numerical values in the range.
#REF! Error value (Individual Cell Reference vs. Range Reference)
Assume cells C24, C25, and C26 contain 1, 2, and 3, respectively, and you have three types of formulas; (Pattern 1) =C24+C25+C26; (Pattern 2) =SUM(C24,C25,C26); and (Pattern 3) =SUM(C24:C26). What happens to these formulas when you delete row 25? The formulas with individual cell references (Pattern 1 and 2) are not updated and return #REF! Error. To fix the issue, you need to take out the #REF! Values from the formulas. On the other hand, the SUM formula referring to a range is automatically updated and still returns the total value of the selected range. Thus, using the SUM with range reference is recommended when you may delete a row(s) or column(s), which is a part of the calculation. The following screenshot shows the three patterns of the formula after removing Row 25, in which the original Row 26 changed to Row 25.
Automatic formula update (Individual Cell Reference vs. Range Reference)
Assume cells C33, C34, and C35 contain 1, 2, and 3, respectively, and you have three types of formulas; (Pattern 1) =C33+C34+C35; (Pattern 2) =SUM(C33,C34,C35); and (Pattern 3) =SUM(C33:C35). What happens to these formulas when you insert a row between Rows 33 and 34 and put a new number there? The answer is that Parttens 1 and 2 still only refer to the original cells (C33, C35 (former C34), and C36 (former C35) and show the same computation results even after the new row is inserted, and the new number is entered. However, as shown in the screenshot below, Pattern 3 is automatically updated. Pattern 3 is recommended when you may insert a new row(s) or column(s), which contains the value you want to sum up together with the original values.
Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows, update numbers in real-time, and save time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. Are you interested in this product but are an Excel user? That’s not a problem at all. You can connect Google Sheets to Excel quickly.
To learn more about LiveFlow, book a demo.
If you don’t get the solution you are looking for in this article, or you have further questions related to mathematics or statistics, you may find the answers in the following articles.
Go to the following articles to learn basic formulas in Google Sheets.
Navigate to the pages below to learn how to sum, count, or average numbers with a condition or multiple conditions.
How to Use SUMIF Function in Google Sheets - sum up the numbers that meet a criterion
How to Use COUNTIF Function in Google Sheets - count the number of cells that meet a requirement
How to Use COUNTIFS function in Google Sheets - count the number of cells that meet multiple requirements
AVERAGEIF Function in Google Sheets: Explained - average the figures that meet a standard
AVERAGEIFS Function in Google Sheets: Explained - average the figures that meet multiple standards
AVERAGE.WEIGHTED Function in Google Sheets: Explained - use this formula to calculate the weighted average
MAXIFS Function in Google Sheets: Explained - seek for the maximum value that meets specific criteria
MINIFS Function in Google Sheets: Explained - seek for the minimum value that meets specific criteria
Visit the following post if you are interested in learning how to count the number of specific cells.
COUNT Function in Google Sheets: Explained - count the number of cells containing numeric values (except for blank ones)
COUNTA Function in Google Sheets: Explained - count the number of cells incorporating all types of values containing text and date, except for blank ones
COUNTBLANK Function in Google Sheets: Explained - count the number of blank cells
COUNTUNIQUE Function in Google Sheets: Explained - count the number of unique cells
You can learn about other Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides
Learn how to do this step-by-step in the video below 👇