Now we have been through the ways and means of referencing cells we can move on to the SUM() function in Excel.
Without doubt the SUM( ) function is one of the most commonly used Excel functions. I will also use the SUM( ) function to better explain what Arguments are in functions and what the Syntax of a Function is.
Arguments
Most of Excel's functions need values of some sort to perform calculations and it is these values that are known as arguments .
The argument for a function could be in the form of a number, text or logical value such as TRUE or FALSE.
They could also be error values or arrays, but we won't go into this in this course.
Some functions will take only one argument while others can take up to 30! The SUM( ) function for example takes up to thirty arguments, but only requires one.
There are other functions that take 5 or 6 arguments and require that all the arguments have a value in them .
💡 Commas are always used to separate arguments in functions.
Syntax
The term syntax in Excel refers to the order in which arguments are accepted in functions.
For example, if we have a function that takes two arguments and the first argument must be a number, while the second must be text, we could not put the text value as the first argument and the number as the second.
If we did we would have the wrong syntax
Now that we have discussed Arguments and Syntax we can move on to creating a formula in Excel using the SUM() function.
The SUM() function
The SUM() function will add all the numbers in a range of cells.
If there is text within the range the SUM() function will ignore them. However, the SUM() function will not ignore text values not stored in cells (a text value is a number entered as text) .
I will use an example to show you what I mean by this statement.
- Type the numbers 1 to 5 in cells A1:A5.
- Now in cell A11 type =SUM()
- Click in cell A1 and holding down the mouse button drag down to cell A10.
- Push Enter (Excel will automatically add our closing parenthesis for us).
You should get the result 15 and if you select cell A11 and look in the formula bar you should see the formula =SUM(A1:A10) .
- If we now type the number 5 in cell A6 we will get the result 20.
- If we type the word dog in cell A7 our result will not change.
- If we then type: "5" (including the quotation marks) the SUM function will again not change
💡 This is because the SUM() function will ignore text entered in cells.
The Anatomy of a Function
Reference another Sheet in your Workbook
If the numbers we wish to sum reside on another Worksheet, we would simply click the sheet name tab first then select the range on the Worksheet.
💡If we want to sum numbers on both the Worksheets housing the sum formula and on another Worksheet, we could use the first argument for the current sheet and the second argument for the other sheet.
💡 If we wanted to sum the same range of cells on different Worksheets that are next to each other, try this exercise:
- Make sure you have five Worksheets in the Workbook you are using. To do this go to Insert>Worksheet as many times as needed.
- Place different data in the range of each worksheet
- In cell A1 of your reporting sheet type =SUM
- Now select the first sheet, this is the sheet on the far left. Hold down your Shift key and select the name tab of the last Worksheet, this is the sheet on the far right.
- Now release the Shift key and select the range you wish the SUM function to sum.
- Push Enter.
❗See the explainer video below
Depending on the Worksheet names, order and range chosen, your formula should look something like: =SUM(Sheet1:Sheet5!D1:G20)
If you now type any numbers within your chosen range on any of the Worksheets, they will affect the result of the SUM() formula accordingly.
💡TIP
If we add another Worksheet anywhere between our chosen sheets it too will have the chosen range included .
Test your knowledge
Bonus Quiz!