Now that we have covered the SUM function, formula arguments and syntax in some detail, let us look at some of Excel's easy to use yet powerful Functions.
Once we have covered these functions we will go into some detail on Excels Insert Function.
The Insert Function houses all of Excel’s built-in functions under their appropriate categories and goes a long way to writing the chosen function for us.
In case you are wondering why we don’t just skip all the detail and go straight to the Insert Function and make life easier for us all, it is because I firmly believe that the most important aspects of Excel and it’s functions and formulas is understanding them.
I like to believe that by course completion I will have taught you Excel, not shown it to you!
The functions we shall be covering today are:
- AVERAGE()
- COUNT()
- COUNTA()
- COUNTBLANK()
- COUNTIF()
- MAX()
- MIN()
- SUMIF()
We will cover the following areas for each function:
- What the function does,
- Followed by its syntax and
- Then the number of arguments it can accept.
It is important to note that while some functions take more than one argument it is not always the case that they must all be supplied. These arguments are known as optional arguments
=SUM(number1,number2,number3,...)
Where: “number1” must be supplied,
While “number2,number3,… number 30” are all optional arguments.
Recall that optional arguments are ignored by the function, unless provided.
Useful Functions
=AVERAGE()
Syntax
=AVERAGE(number1,number2, ...)
The AVERAGE() function can take up to 30 arguments.
- The arguments supplied must be numeric or references to numeric values.
- Text and/or references to text are ignored.
- It is important to note that cells containing zeros are NOT ignored.
- This can give you unexpected results, if you are not aware of it.
=AVERAGE(A1:A3) would equal 10, if A1:A3 contained 5, 10, 15 respectively.
=COUNT()
Syntax
=COUNT(value1,value2, ...)
The COUNT function takes up to 30 arguments .
- Each argument can be a variety of data types, but only numbers are counted.
- If the range reference supplied contains valid dates these will also be counted.
=COUNT(A1:A5) would equal 3, if cells A1:A5 contained:
10,
12/12/2001,
House,
0,
Dog
=COUNTA()
Syntax
=COUNTA(value1,value2, ...)
The COUNTA() function takes up to 30 arguments .
Each argument should be a reference to a range. ,
Cells within the range can be a variety of data types. However, EMPTY cells are ignored.
=COUNTA(A1:A5) would equal 5, if cells A1:A5 contained:
10,
12/12/2001,
House,
0,
Dog
=COUNTBLANK()
The COUNTBLANK() function is used to count empty cells.
It is the opposite of the COUNTA function
Syntax
=COUNTBLANK(range)
The COUNTBLANK() function takes 1 argument .
Each argument should be a reference to a range. ,
Cells within the range can be a variety of data types, but only non-empty cells are counted.
=COUNTBLANK(A1:A5) would equal 1, if cells A1:A5 contained:
"",
12/12/2001,
House,
0,
Dog
NOTE: The first item is a blank cell
Because dates are actually numbers that are just formatted to look like dates
=COUNTIF()
The COUNTIF() function is used to count cells within a range that meet a specified criterion.
Syntax
=COUNTIF(range,criteria)
The COUNTIF() function takes two (2) arguments.
- The range argument is a reference to a range of cells,
- The criteria argument is the criterion that should be met by the cells within range before they are counted.
- Criteria specified can be in the form of
- A number,
- Text or
- An expression
=COUNTIF(A1:A5,20) would equal 1, if cells A1:A5 contained:
15,
22,
20,
0,
Dog
A3 is the only cell that meets the criteria of 20
=COUNTIF(A1:A5,"dog") would equal 1, if cells A1:A5 contained:
15,
22,
20,
0,
Dog
A5 is the only cell that meets the criteria of "dog" .
=COUNTIF(A1:A5,"<20") would equal 2, if cells A1:A5 contained:
15,
22,
20,
0,
Dog
A1 and A4 are the only cells that meets the criteria of "<20".
=SUMIF()
The SUMIF() function is used to return the sum value from a specified range that meets a criterion.
Syntax
=SUMIF(range,criteria,sum_range)
The SUMIF() function takes up to 3 arguments.
- The range is the range of cells to evaluate to see if they meet the specified criteria.
- The criteria specified can be in the form of:
- A number,
- Text or
- An expression.
- The sum_range is the range of cells to sum, but only if the corresponding cells in the range meet the specified criteria.
If sum_range is omitted then the cells within the range are summed.
Example 1:
=SUMIF(A1:A5,5) would equal 10, if cells A1:A5 contained:
5,
8,
House,
10,
5
In other words cells A1 and A5 would be summed as they meet the criteria and
NO sum_range was supplied.
Example 2:
=SUMIF(A1:A5,5,B1:B5) would equal 20, if cells A1:A5 contained:
5,
8,
1,
9,
5
AND if cells B1:B5 contained:
10,
1,
3,
8,
10
In other words cells B1 and B5 would be summed as the corresponding
cells in A1:A5 have a value of 5.
Example 3:
=SUMIF(A1:A5,”Cat”,B1:B5) would equal 15, if cells A1:A5 contained:
Cat,
Cat,
Cat,
9,
Dog
and cells B1:B5 contained:
5,
5,
5,
5,
11
In other words cells B1,B2 and B3 would be summed as the corresponding
cells in A1:A5 contain the text “Cat” (not case sensitive).
Example 4:
=SUMIF(A1:A5,”>5”) would equal 34, if cells A1:A5 contained:
10,
15,
Cat,
9,
5
In other words cells A1 and A5 would be summed as they
meet the criteria of being greater than 5.
Insert Function
Formerly known as the Paste Function dialog in older versions of Excel, the Insert Function in newer versions, this dialog box is used to insert or paste the selected function into the chosen cell.
This feature is can be used as a step-by-step guide for each argument in a function.
The Insert Function is very handy when writing slightly harder functions such as COUNTIF(), SUMIF() etc.
Let’s display the Insert Function dialog and have a superficial look at it.
There are three methods we can use to show this dialog box and which one you use is purely optional:
The three methods are:
- Going to Insert>Function
- Push Shift + F3
- Click the Insert Function icon to the left of your Formula bar (Fx),
For older version users, click the Paste Function icon on your Standard toolbar.
Once activated you will see the Insert Function dialog pop up in front of you.
Depending on which version of Excel you are using, these heading names may vary slightly in this dialog box.
How to use Insert Function to build a SUMIF() formula
A Practical Example
Use the BUTTON below to access a spreadsheet that you can use to test your skills and practice the functions you've learnt 😊
Answers are provided on a separate worksheet in the same workbook