As one of Excel’s most used functions, the SUM function can add either values, values in cells, or values across cells in a given range or ranges.
Function Syntax
=SUM(number1,[number2],...)
Usage
The SUM function will add together numeric values as a list of arguments separated by commas. These arguments must be numbers, or cell references to numbers.
Only a single argument is mandatory in its simplest form, which will trivially return that number.
eg. SUM with a single argument:
=SUM(12)
will output:
12
SUM will generally be used to sum multiple values or arguments
Summing multiple individual values
=SUM(1,2,3)
Summing multiple individual cells
=SUM(A1,A2,A3)
Summing a cell range – note here that only one argument is used (a range)
=SUM(A1:B2)
Summing can also include a mix of values, cell values, and cell ranges
=SUM(1,2,A1,A2,B1:B10)
The Excel SUM function can accept up to 255 individual arguments. However each argument can consist of a range of cells. Therefore the total number of values being summed in the range is not limited directly by the number of arguments SUM can accept.
NOTE: As a general rule, it is best practice not to ‘hide’ static values inside functions as arguments. A better practice would be to put each value in a cell, labelled appropriately, and for the function to reference those cells’ values.
That way the spreadsheet user can easily update a cell’s value and be sure that all functions using that value will be updated simultaneously without having to search for, and edit, individual functions which use the same value.
Examples
SUM individual values
The simplest use for SUM is to provide individual values to the SUM function
SUM individual cells
Most of the time however you will want to use SUM to tally or total the values in other cells, or a range of cells.
SUM cell range
Where cells are contiguous (all touching each other) and in a rectagonal grouping the whole range can be referenced inside the function.
SUM entire column
An entire column can be passed in as a range and summed, by specifying only the column and not the rows in the function argument.
SUM values with text
Note that the SUM function will ignore cells with text in, without raising an error.
SUM entire row
A whole row can also be summed, with text fields being ignored.
SUM with mixed argument types
So far we have looked at values, cells and ranges passed in as arguments to the SUM function. Where values are not contiguous (ie. in cells not touching one another), a mix of argument types can be used, including values, cell references and range references.
SUM with AutoSum
Don’t forget Excel’s AutoSum functionality. A quick option allowing Excel find the range to be summed over.
HINT: Actually the AutoSum function includes other popular calculation options which user may want, like Average, Count Numbers, Max & Min. But by default, AutoSum activates the SUM function.
AutoSum – step approach
To insert the SUM function from the Formulas Ribbon, first click on the cell at the end of a column of data you which to total up. This will be where the SUM function will be inserted. As well as it being the obvious place to add a total, it also enables Excel to identify the most likely neighboring cell range to sum over.
To Access AutoSum, select the Formulas tab in the Ribbon at the top of the application.
In the Functions Library section of the Formulas Ribbon, click the ‘Σ‘ (Sigma) symbol of the AutoSum button.
HINT: Selecting the small down arrow at the bottom will give you access to the Average, Count Numbers, Max & Min options.
AutoSum will select relevant contiguous cells which it believes are to be summed. Click Enter to accept the selection, or edit the selection as required.
HINT: As shown when hovering over the AutoSum button, the shortcut ALT + “=” can be used instead to AutoSum
Once confirmed the function will return the SUM total of the function.
HINT: So, that looks a lot of hassle to sum a few cells, when you can just type =SUM({your_range}) directly into the cell. But the AutoSum button or shortcut become particularly useful when you have a very large spreadsheet of values. In that case scrolling to find the start or end point of a range becomes more problematic than clicking a couple of buttons or knowing the shortcut.
AutoSum – Shortcut
AutoSum also has a keyboard shortcut which might save significant time. As with all shortcuts, there are generally too many to learn them all off by heart, so its about picking up the ones you are most likely to use or which would save you the most time.
Select a cell at the bottom of a range of values you wish to SUM above, or a cell to the right of a range of values (to the left). Typing the two shortcut keys simultaneously: ‘ALT’ & ‘+’, will insert the SUM function and also select the the contiguous cells in that column (above) or row (to the left). It will even include initial blank cells to find the first value in contiguous range.
How to use the AutoSum Shortcut
ALT +
Select a Cell below or to the right of your value range.
Click the AutoSum shortcut ‘ALT +’ to insert the function.
Related Topics
- Other Excel Functions
- Other Excel Math Functions
F002