Excel Functions: SUM, AVERAGE, COUNT, MIN, MAX
A function is a ready-made formula. You give it some values, and it gives back a result.
How a Function is Written
Example
=FUNCTIONNAME(argument1, argument2, ...)- Start with
= - Write the function name (not case sensitive)
- Put the inputs, called arguments, inside brackets, separated by commas
- Arguments can be numbers, cells, ranges or even other functions
In some regional settings Excel uses a semicolon ; instead of a comma between arguments. If a comma gives an error, try a semicolon.
The Example Data
A class result sheet for this chapter:
| A | B | |
|---|---|---|
| 1 | Student | Marks |
| 2 | Ali | 78 |
| 3 | Ayesha | 91 |
| 4 | Bilal | 65 |
| 5 | Sana | absent |
| 6 | Hamza | 84 |
The Most Used Functions
Example
=SUM(B2:B6)
=AVERAGE(B2:B6)
=COUNT(B2:B6)
=COUNTA(B2:B6)
=MIN(B2:B6)
=MAX(B2:B6)| Function | What it does | Result |
|---|---|---|
SUM | Adds the numbers | 318 |
AVERAGE | Average of the numbers | 79.5 |
COUNT | Counts cells with numbers | 4 |
COUNTA | Counts cells that are not empty | 5 |
MIN | Smallest number | 65 |
MAX | Largest number | 91 |
Notice that "absent" is text, so SUM, AVERAGE, COUNT, MIN and MAX ignore it, while COUNTA counts it.
AutoSum
The quickest way to add a column: click the cell under the numbers and press Alt + =. Excel writes the SUM formula and guesses the range. Press Enter to accept.
The arrow next to the AutoSum button (Home tab) also offers Average, Count, Max and Min.
Rounding Numbers
ROUND rounds to a number of decimal places:
Example
=ROUND(79.456, 1)
=ROUND(79.456, 0)
=ROUND(AVERAGE(B2:B6), 0)| Formula | Result |
|---|---|
=ROUND(79.456, 1) | 79.5 |
=ROUND(79.456, 0) | 79 |
=ROUND(AVERAGE(B2:B6), 0) | 80 |
The last example puts one function inside another. Excel calculates the inner one first. Formatting a cell to show fewer decimals only changes how it looks; ROUND changes the actual value.
Counting and Adding with Conditions
COUNTIF counts cells that meet a condition, and SUMIF adds values that meet a condition.
Ali's shop sales:
| A | B | |
|---|---|---|
| 1 | Category | Amount |
| 2 | Grocery | 1200 |
| 3 | Bakery | 450 |
| 4 | Grocery | 800 |
| 5 | Drinks | 300 |
| 6 | Bakery | 650 |
Example
=COUNTIF(A2:A6, "Grocery")
=SUMIF(A2:A6, "Bakery", B2:B6)
=COUNTIF(B2:B6, ">500")
=SUMIF(B2:B6, ">=500")| Formula | Result | Meaning |
|---|---|---|
=COUNTIF(A2:A6, "Grocery") | 2 | Two grocery sales |
=SUMIF(A2:A6, "Bakery", B2:B6) | 1100 | Total bakery sales |
=COUNTIF(B2:B6, ">500") | 3 | Sales over Rs 500 |
=SUMIF(B2:B6, ">=500") | 2650 | Total of sales of Rs 500 or more |
For more than one condition, use COUNTIFS and SUMIFS:
Example
=SUMIFS(B2:B6, A2:A6, "Grocery", B2:B6, ">1000")| Result |
|---|
| 1200 |
Inserting a Function
If you do not remember a function's arguments:
- Click the fx button next to the formula bar to search for a function and fill a form
- Or start typing
=SUM(and Excel shows a small tip with the arguments
Exercise
Exercise
A range has the values 10, 20, the text "N/A" and 30. What does =COUNT(range) return?