In the previous post we showed how moving data around can impact a sum formula. Below we will go over what a Sum is and the various ways that you can construct it.
A sum function is one of the basic Excel math functions that you can use to add data together to get a total. You can add together specific cells and ranges of cells.
You can find the function by either:
- Typing into a cell or in the formula bar and typing
- Clicking on the function
- Choosing AutoSum from the menu
The below data will be used for the examples in this post and we will be summing data in the highlighted cell
Typing Into a Cell or the Formula Bar
I want to encourage beginners in Excel to use this option as it allows the most flexibility in letting you sum the data that you need to. Most users want to default to the aforementioned AutoSum which does the summing automatically for you, but learning how to type the formula out will help you in the long run.
Click in cell
E10 to select the cell.
As you type, you will see excel display the rage in your formula with a colored outline
Note that when you click on E10 again it will show you your formula in the formula bar. You can also double click E10 to enter the cell and see the outlined sum range
Clicking the function button will allow you to perform a search for various functions in excel. If you do not already see SUM, type it into the top search box and hit “Go” to search for it.
Select SUM and hit OK
This will take you to a dialog box which allows you type in multiple sum formulas.
You can type in
E2:E9 in the first box to get the same SUM result we got in the earlier example.
Alternatively, let’s sum values with the name “Gram,Lilly”.
Number 1 will be
Number 2 will be
Number 3 will be
The formula result should be:
Note how the cells are separated by commas and lack the colon “:” from the
E2:E9 example. The colon defines a range while the comma separates pieces of the formula.
Let’s SUM the values with the names “Gram,Lilly” and “Page,Holly”.
You could set your formula like this, referencing the individual cells:
or you could set it like this, combining the cells into a range using the colon.
Note how the different formulas change the outline color of the cells.
AutoSum is my least preferred method of summing information as Excel does the sum automatically for you, offering you the least control. Granted, you can always change the formula once the AutoSum is done, but if you are going to do that, you might at well just make the formula yourself to save time. AutoSum will default to trying to sum cells above where you are placing the formula. It will stop if there is a blank cell. If there is no data above, then it will try and sum cells to the left of the formula.
On the Home tab, find AutoSum in the editing section. Click it once to show the range it is going to sum. Hit enter to finalize it.
The below GIF will show three variations of the AutoSum formula:
AutoSum of the entire column
AutoSum that stops at first blank cell
AutoSum that Sums left instead of above