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 "+" 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.
=SUM(number_1, [number_2], ...)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | 5 | 8 | |||
| 3 | |||||
| 4 |
Like all functions in Excel, SUM begins with an equals sign (=) to let Excel know it's a function, and not just text. As I mentioned before, you can add the numbers simply by typing =5+8. You can also use cell references in the function. To use cell references, you could type =B2+D2 which would return the sum of the numbers contained in the cells above. This method is fine to quickly add a few numbers, but can quickly 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. The following sum function takes cells B2 and D2 as its arguments and returns their sum:
=SUM(B2, D2)The SUM function above would return a value of 13.
Excel will let you keep adding arguments separated by commas, so you can continue to add arguments to the function above and the function 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.
However, if you want to find the sum of multiple adjacent cells, it can be more efficient to pass a range of cells as an argument to the SUM function.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | 5 | 4 | 8 | 2 | |
| 3 | |||||
| 4 |
Excel can quickly sum an entire group of adjacent cells if you pass 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, to add each cell from B2 to E2, you would use the SUM function with a single range argument "B2:E2". The following formula will return the sum of all the numbers contained in cells B2, C2, D2, and E2:
=SUM(B2:E2)The SUM function above would return a value of 19. A range in Excel may contain any number of cells.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | 5 | 4 | 8 | ||
| 3 | 3 | 7 | 5 | ||
| 4 | 9 | 0 | 2 |
You can use functions with different types of cell ranges in Excel. In addition to "horizontal" ranges like B2:E2 mentioned above, you can also use "vertical" ranges across multiple rows:
=SUM(B2:B4)This SUM function would return 17.
You can even use a "2 Dimensional" range across multiple rows and across multiple columns. To add every number in the table above, you can use the following:
=SUM(B2:D4)Sometimes in Excel you may want to find the sum of every single number that appears in a specific column, regardless of its row. The following formula returns the sum of the entire C column.
=SUM(C:C)The formula above would return 11, because this is the sum of every number in column C.
You can also find the sum of every number in a specific row. Say you wanted the sum of every number in row 4.
=SUM(4:4)This formula would also return 11, as this is the sum of every number in row 4.
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 function or 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.
=SUMIF(A:A, ">5")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.
Watch the video below to see the SUM function in action!