Lesson 18 - Excel Formulae Arguments & Syntax

Category: [Excel] Demo Available

# EXCEL FORMULAE ARGUMENTS & SYNTAX

## Lesson 18 - Excel Arguments and Syntax in Formulas

### EXCEL FORMULAE ARGUMENTS & SYNTAX

###

### Gallery

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 Excels 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 at this level.

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 we have discussed **Arguments** and **Syntax** we can move on to creating a formula in Excel using 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.

To see what I mean by my previous statement "*However the SUM function will not ignore text values not stored in cells*" we will need to add another

- Select Cell
**A11**Then Place Your Mouse Insertion Point Between The**A10**And The Closing Parenthesis. - Type A
**Comma**(This Tells Excel We Are Adding Another Argument) Then Type "**5**" (Including The Quotation Marks) - Push
**Enter**.

This time our result in cell **A11** will change to **25**. This shows that while the **SUM** function **will** ignore text values stored in cells it does **not** ignore text values enter directly as an argument.

The range of cells that we use as an argument in the **SUM** function do not have to be adjoining as in the above example (**A1:A10**) they can be in non-adjoining ranges. This is the most likely reason we would use more than one argument.

- Delete The
**SUM**Formula In Cell**A11**. - Type The Numbers
**1**To**5**In Cells**D1:D5**. - Type
**=Sum(**In Cell**A11**Then Select The Range**A1:A10**. - Type A Comma (,) To Start Another Argument.
- Select The Range
**D1:D5**And Push**Enter**.

You should get the sum value of all numbers in the ranges **A1:A10** and **D1:D5**

We can make this task slightly easier by selecting the range **A1:A10** then holding down the **Ctrl** key, select range **D1:D5** and up to another **28** ranges if we wanted to.

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 we could do so like this:

- Make Sure You Have Five Worksheets In The Workbook You Are Using. To Do This Go To
**Insert>Worksheet**As Many Times As Needed. - In Cell
**A1**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**.

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. We should also note that if we add another Worksheet anywhere between our chosen sheets it too will have the chosen range included (**D1:G20** in my example).

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Go back to:

See also:

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.