There are plenty of ways to add numbers in Excel, and the SUM function is often written off as boring or simple. The SUM function, while simple on the surface, is more powerful than it typically gets credit for and can save you lots of time if you utilize it to its full potential. Sure, you can use Excel's basic addition (+) operator, but you can also use the SUM function to add several numbers, or a range of numbers, quickly and efficiently with a smaller chance of error.
The SUM Function requires only one argument (or "parameter") but can take up to 255 total arguments. The SUM function will return the sum of all arguments.
=SUM(number_1, [number_2], ...)The SUM function requires at least one numerical argument (number inside the parentheses separated by commas).
Each additional argument in the SUM function is optional. The SUM function requires only one argument, but can optionally have any number of additional arguments, up to a maximum of 255 total arguments.
Like all functions in Excel, SUM begins with an equals sign (=) to let Excel know that it's a function, and not just text. As I mentioned before, you could find the sum of Mac revenue in the example below simply by typing =28,622 + 25,740 + 25,198. But this would be difficult and there's a high likelihood you would type one of the numbers incorrectly.
You can also use cell references in the formula. To use cell references, you could type =E6 + F6 + G6 which would return the sum of the numbers contained in the cells. This method is fine to quickly add a few numbers, but will soon become tedious if you're asking Excel to find the sum of a long list of numbers. It is better practice to utilize the SUM function.
The SUM function returns the sum of all arguments, or values inside the function parentheses separated by commas. Think of a function's arguments as its inputs. To get the total iPhone revenue across all three years, double-click cell H5 (column H, row 5) in the spreadsheet above and type the following formula:
The SUM formula will return a value of $445,050, because this is the sum of the numbers contained in cells E5, F5, and G5.
Excel will let you keep adding arguments separated by commas, so you can continue to add arguments to the formula above and it will return the sum of all of the arguments provided. In fact, while only one argument is required for this function, Excel will let you add up to 255 individual arguments.
Of course, if you're adding many numbers in a range of cells together, it's easier to pass a range of cells into the SUM function.
Excel can quickly sum an entire group of adjacent cells if you pass in a range of cells as an argument to the SUM function. A range of cells is denoted by two cell references with a colon (:) between them. For example, say you wanted to find Apple's total revenue for 2020 in the spreadsheet above. You could type in every cell reference (E5, E6, E7, E8, E9), or you could simply pass in the entire range of cells from E5 to E9 (E5:E9).
Double-click into cell E10 and type this formula to get Apple's total revenue for 2020:
This formula should return a value of $274,515, because this is the sum of the numbers contained in cells E5, E6, E7, E8, and E9. A range in Excel can contain any number of cells.
In addition to the vertical range we used above (E5:E9), you can also use horizontal ranges of cells. Say you wanted total Mac sales across all three years. Double-click into cell H7 and type the following formula:
Say you wanted to find the sum of all iPhone sales, regardless of how many years were included in the spreadsheet. In this case you would want to find the sum of all numbers in row 5. To do this, you can use the range (5:5) to tell Excel "everything in row 5."
Note that if you use this formula, you cannot write it in row 5, because it will include itself in the sum and keep growing forever, resulting in a circular reference error.
Similarly, if you wanted to add all the numbers in column H in the example above, you could pass in the range H:H as in the example below:
Just like when using horizontal ranges in Excel, you couldn't write this formula in column H because you would receive a circular reference error.
Finally, if you wanted the sum of all products across all three years, you could pass in a two-dimensional range of cells. To find the total of all cells in the spreadsheet example above, double-click into cell H10 and type the following formula:
Another way to quickly sum up a group of cells is to use the Autosum shortcut in Excel.
Finally, Excel lets you mix and match the different types of arguments that you pass to the SUM function. As stated above, SUM can accommodate single cell references as arguments (such as A1), as well as a range of cells (such as A1:B5). SUM can also accommodate plain numbers (such as 5) as arguments, as well as another Excel formula. For example, =SUM(5, A1) is a valid function that will return the sum of cell A5 plus 5. Additionally, =SUM(5, (10/5)) is another valid function that will return the sum of 5 and 2. In fact, any or all of these types of arguments can be used with SUM:
For an extreme example, the following formula:
is a valid function that returns the sum of 5, cells A3, A2, B2, C2, D2, and 15. As long as you have 255 arguments or fewer, any numerical value is fair game.
If a certain cell reference or range of cells contains text, Excel will simply ignore the text and only return the sum of the cells that contain numerical values.
Sometimes in Excel you may wish to add together several numbers which meet a certain condition. Say, for example, that you want to add together every number in column A that is greater than 5. In this case you would use a function called SUMIF to add numbers which meet a certain condition.
The function above would add every number in column A that is greater than 5. Read more about the SUMIF function now!
If you want the sum of all the numbers which meet multiple conditions, see the SUMIFS function.
