Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
The Purpose of the SUM Function
The SUM function is used to add values together from different cells in a worksheet or add numbers directly in a formula. This may be one of the most widely used functions in the history of Excel. Using the SUM function has proven to be a shortcut to adding numbers to a worksheet.
In this article, you will find out how the SUM function is used as well as the many ways it can be practically applied in Excel. Additionally, you will see how to add the function to Excel using four different methods.
The SUM Function Syntax
The syntax for the SUM function is shown here:
The SUM function is very straightforward. Numbers and cell references can be used to get a sum of multiple numbers. A range is usually used as a reference to collect data for this function. The function needs to be inputted like a formula to work properly. To manually add this formula to a cell, the following steps need to be followed:
- A cell needs clicked and =SUM( needs to be typed.
- After the open parenthesis, data is referenced are entered (numbers, a range, named ranges, a cell reference).
- A closed parenthesis is used to close the formula.
- The enter button can be pressed to return the sum of the data.
Summing a Range of Data
You would sum a range like in the illustration below when all of the data that needs to be added together can be found in the same area of your spreadsheet.
Data can be selected vertically or horizontally with multiple rows and columns. An example of this technique can be seen in the illustration below.
Sum With Cell References
You may opt to sum using cell references. This will allow you to sum from a vast number of cells from different areas of a workbook.
Each reference needs to be added to the formula and separated by a comma. This technique can be seen being used in the illustration below.
Sum Using Numbers
You can also sum with just numbers alone without using any ranges or references. This might be useful when a set of numbers always stays the same, and you want to add a cell reference as a variable to the set of numbers.
An example of using numbers only in the SUM function can be seen in the illustration below.
SUM Using Named Ranges
Lastly, you may also sum named ranges of numbers as shown in the illustration below. Named range1 outlined in blue is being added to range2 outlined in red.
It's worth noting that each of the 4 examples described can be used together interchangably. Ranges can be used and followed by cell references, numbers and named ranges as long as the correct syntax is used.
Inserting the SUM Function
The SUM function can be inserted into a cell using two methods. In one method, you search for the function from the function library, and the other involves the use of an insert function button. Each can be completed in a few steps and is described in the following sections.
Inserting From the Formula Library
To use this method, a cell is first selected. Next, the formulas tab is selected, and the "Math and Trig" button is be chosen from the function library. Next, SUM can be chosen from the dropdown menu. These steps are shown here in bold and in the illustration below.
Formulas → Math & Trig → SUM
After the functional arguments window appears, you have the option to add as many as 255 numbers, cell references, or ranges that contain 255 cells.
Each time you enter a number or reference into a field, a new one will appear for the next data entry. Once you have added the numbers and/or references that you need, click on the OK button. The functional arguments window can be seen in the example illustration below.
Inserting From the Insert Function Button
Another way to insert this function would be to insert it from the insert function button. This button is also found under the formulas tab and located on the upper left side of the Excel ribbon in the function library.
Formula Tab → Insert Function
After clicking on the insert function button, the insert function window appears. Text can be entered to search for the function that you are looking for. After clicking OK, you will be directed to the functional arguments window.
Using the SUM Function Shortcut
There is an easy way to bring up the SUM function when you need it. Instead of typing out the formula or inserting the function, you can use a three-stroke shortcut. By holding alt + = you can get the function to appear after you have clicked in a cell. The option will not help you fill the formula like the last two insert options, but it will save you some keystrokes when entering the function manually.
Microsoft. (n.d.). SUM function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89.
- Converting Measurement Units: Using the Convert Function in Microsoft Excel
Describes how to use the CONVERT function within Microsoft Excel to convert various types of measurement units to other types of units. Types of conversions include: weight and mass, distance, time, pressure, force, energy , power, magnetism, tempera
- How to Use the VLOOKUP Function in Excel
Covers how to use the VLOOKUP function in Microsoft Excel with examples on the correct syntax to use.
- How to Remove Errors in Excel With the IFERROR Function
Shows a Microsoft Excel user how to apply the IFERROR Function to remove errors in cells.
- How to Use the ABS Function in Excel
Covers how to use the ABS function in Microsoft Excel with examples on the correct syntax to use.
- How to Use the AVERAGE Function in Excel
Shows how to use the AVERAGE function in Excel with application examples.
- How to Use the COUNT Function in Excel
Shows a Microsoft Excel user how to use the count function in a spreadsheet.
To learn more about using functions in Excel I recommend purchasing Excel Formulas & Functions For Dummies.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2021 Joshua Crowder