If you have a large database of information, it can be difficult to make sense of all those names numbers. The Excel SUM formula lets you focus on specific categories within an Excel worksheet and come up with subtotals that can help you to spot trends and patterns in your data.
Read on to find out more about:
- How the SUM function works
- Using the Excel SUM function
- Different applications for SUM formulas
About the SUM Function
The SUM function is one of the simplest functions in Excel, but it’s also one of the most useful. It’s one of several math and trig functions available and allows you to add up multiple values. You can add up individual numbers, cell references, ranges of cells, or mix these values together. You can also use the SUM function as part of a more complicated order of operations to manipulate values. For example:
- Addition: =SUM(A1:A10)+B1
- Subtraction: =SUM(A1:A10)-B1
- Multiplication: =SUM(A1:A10)*B1
- Division: =SUM(A1:A10)/B1
- Exponentiation: =SUM(A1:A10)^B1
How to Use the SUM Function
The Excel SUM formula is easy to use and easy to remember, even for complete beginners. The basic syntax of the function goes as follows:
=SUM(number1,[number2],…)
You only need to input a single range or value into the brackets, but if needed, you can include as many as 255 numbers. The parameters you’ll be using include:
- number1: This value is required for the function to work properly. You can use a number such as 4, a cell reference such as A4, or a cell range like A1:A4.
- number2-255: While you don’t have to input this value, if necessary, you can add hundreds of secondary numbers to your formula.
The SUM formula is a worksheet function, meaning that to use it, you simply type the appropriate syntax into a free cell to find out your end result. You can also use it in tables to total up rows and columns.
If you don’t want to display totals on your worksheet, you can find out the sum of a group of cells using the Excel status bar. Simply click and select the range, then look at the sum total listed in the lower right-hand side of the window.
Using SUM With Other Excel Functions
Excel has some built-in formulas that combine the SUM function with other useful tools. For example, combining SUM with the IF formula creates the SUMIF function, which allows you not only to add up data points, but also to choose these points based on criteria such as date, numeric value, text descriptors, and more.
You can also create your own formulas that combine the SUM function with other actions. Combining the operations of two or more functions is commonly known as “nesting functions.” There are endless ways that you can nest SUM with different Excel formulas. For example, you can combine SUM with the ROUND function to round decimals and fractions before adding them up, which can be handy come tax season.
Combining SUM with the VLOOKUP allows you to locate and sum values with that meet one or several criteria. No matter what the application, you can find a way to nest SUM with other Excel functions to get the results that you need.
Using Excel SUM Formula Across Multiple Worksheets
When using the SUM function, you might not always be dealing with values on a single spreadsheet. For example, if your company uses a different worksheet for each month or quarter, you may need to total data across several separate datasets. Instead of manually adding each relevant cell, it’s much easier to use a 3-Dimensional (or 3D) SUM.
The syntax to add a cell value from different sheets reads:
=SUM(SheetX:SheetY!cell#)
Common Problems With Using Excel SUM Formula
Although the Excel SUM formula is relatively straightforward, every now and then even the most tech-savvy of us run into issues. Here are some common problems you may run into when using SUM functions, and quick fixes to help save you a headache:
The result is displayed as ####: Often, this reading simply means that your column is too narrow to display the full result. Increase the width of your column to prevent this problem. | |
The formula won’t change to a result: The cell containing your SUM function may be formatted as a text cell, preventing the formula from working. Hit Ctrl+1 to bring up the Format Cells dialog, then click the Number tab to select the right format. You can also use F2 > Enter. | |
The function won’t update automatically: Open the Formula tab and go to Calculation to check that it’s set to automatic. You can also use the F9 key. | |
The #NAME? error appears: This usually means that a formula’s syntax is incorrect. Check to make sure that everything is spelled correctly and in the right order. |
Examples of SUM Formulas
As an Excel user, you’re bound to run across the SUM function in various ways, shapes, and forms. Here are some of the more common formulas that utilize this tool:
When inputting individual cells or values: | =SUM(A1,A3,B2,C3…) |
When inputting a range of values: | =SUM(A1:A3,B1:B3) |
Sum an entire column: | =SUM(A:A) |
Sum every nth row: | =SUM(OFFSET(A1,(ROW()-offset)*n,0,n,1)) |
Calculating a running total: | =SUM($A$1:A1) |
Conclusion
The SUM function is a must-know tool for all Excel users, whether you’re just starting out or working with Microsoft on a professional level. The Excel SUM formula can help you to add points from complex sets of data and gain valuable insights that will help you or your company to make more well-informed decisions in the future.
We hope that our comprehensive guide has helped you to better understand the many uses of the SUM formula and hone your skills so that you can take full advantage of this invaluable tool.