BeginnerLesson 4 of 305 min read

Functions: SUM, AVERAGE, MIN, MAX and COUNT

Use Excel's ready-made functions to total, average and count data, with real results, AutoSum and tips for choosing ranges.

In this lesson you will

  • Write functions with ranges
  • Choose between COUNT and COUNTA
  • Use AutoSum and check its range
  • Round results

About 14 minutes

From a column of numbers to the answers you need

Rohit teaches maths to six students and wants quick answers: the total marks, the class average, the highest and the lowest mark. Typing a long formula such as =B2+B3+B4+B5+B6+B7 works for six students, but not for six hundred. Excel gives him functions: ready-made tools that do the work from a short name.

What a function looks like

=FUNCTIONNAME(what to work on)

The name tells Excel what to do. The brackets hold the arguments, the cells or values to work with. For a range, you give the first and last cell with a colon, so B2:B7 means all six marks.

The five functions you will use most

Function What it does
SUM Adds the numbers
AVERAGE The mean: the total divided by the count
MIN The smallest number
MAX The largest number
COUNT How many cells contain numbers

Real results

Here are Rohit’s marks in B2 to B7: 72, 85, 64, 90, 58 and 85. In B8 there is the word ‘absent’ for a seventh student. The results below were really calculated:

Formula Result
=SUM(B2:B7) 454
=AVERAGE(B2:B7) 75.666667
=MIN(B2:B7) 58
=MAX(B2:B7) 90
=COUNT(B2:B7) 6
=ROUND(AVERAGE(B2:B7),1) 75.7
=LARGE(B2:B7,2) 85
=COUNTA(B2:B8) 7

Notice three things. First, AVERAGE gave a long decimal, so ROUND(…,1) trimmed it to one decimal place. Second, COUNT counted six numbers but ignored the word ‘absent’, whereas COUNTA counted all seven non-empty cells. Use COUNT for numbers and COUNTA for anything that is not blank. Third, LARGE(range,2) returned the second largest mark, 85. The same mark 85 appears twice, so the second largest is also 85.

AutoSum: the quick route

  1. Click the empty cell under your column of numbers.
  2. On the Home tab, click AutoSum (the Greek letter sigma), or press Alt+=.
  3. Excel guesses the range and shows it with a moving border.
  4. Check that the range is right, then press Enter.

The dropdown next to AutoSum offers Average, Count Numbers, Max and Min too. Always check the range Excel guessed: if there is a gap or a title above, it may choose the wrong cells.

Ranges with several parts

  • =SUM(B2:B7): one range.
  • =SUM(B2:B4, B6): separate parts, joined with commas.
  • =SUM(B2:D7): a block of rows and columns.
  • =SUM(B:B): the entire column B. Handy for growing lists, but do not put the formula inside column B.

Function tips

  • Start typing =SU and Excel suggests function names; press Tab to accept one.
  • After typing the name and an opening bracket, a small tooltip shows what the function expects.
  • Click the fx button beside the formula bar to search for any function and see help.
  • Functions can be put inside each other, as in ROUND(AVERAGE(…),1). Work from the inside out.

Why use a function when you could add by hand?

Because a function adjusts. Insert a new row inside the range and SUM includes it. Change a mark and the average updates. And you cannot forget a cell, which is the commonest mistake with long hand-typed additions.

Practice

Type ten daily temperatures, step counts or expenses into a column. Beneath them, use functions to show the total, average, lowest, highest and how many entries there are. Round the average to one decimal place. Then add one blank cell and one word in the range, and check which functions change.

Practice answer

You should find that SUM, AVERAGE, MIN, MAX and COUNT all skip blanks and words, but COUNTA counts the word. AVERAGE divides by the number of numeric cells only, so a blank is not treated as zero. If you want a zero, type 0.

Common mistake

Letting AutoSum pick the range without looking. If a stray number sits in the column above, the total will include it.

Choosing the right function

When you face a new question, say it in plain words first and then match it to a function. ‘How much in total?’ is SUM. ‘What is typical?’ is AVERAGE. ‘What was the best result?’ is MAX and ‘the worst’ is MIN. ‘How many entries do I have?’ is COUNT for numbers or COUNTA for anything. Speaking the question aloud makes the choice obvious, and the same habit will serve you for every function you meet later.

Plain question Function
How much did we sell in total? SUM
What is the usual order size? AVERAGE
What was the biggest or smallest? MAX or MIN
How many students sat the exam? COUNT
How many rows have an entry? COUNTA

Reading the tooltip

When you type a function name and the opening bracket, Excel shows a small tooltip such as SUM(number1, [number2], …). Square brackets mean an argument is optional. The argument in bold is the one you are typing now. Click a name in the tooltip to open the full help page, where you will find examples you can copy. Learning to read these tooltips is the fastest route to learning any new function by yourself.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

Which function adds numbers?

  1. MAX
  2. COUNTA
  3. SUM
  4. ROUND

SUM adds all numbers in its range.

What does B2:B7 mean?

  1. Only B2 and B7
  2. B2 divided by B7
  3. Row 2 to row 7
  4. Every cell from B2 to B7

The colon joins first and last cell.

Which function counts all non-empty cells, including words?

  1. COUNT
  2. COUNTA
  3. SUM
  4. MIN

COUNT counts only numbers.

What does the Alt+= shortcut do?

  1. Saves the file
  2. Prints
  3. Starts AutoSum
  4. Opens help

It inserts a SUM for you.

Why should you check the range AutoSum picks?

  1. It can guess wrongly
  2. It is always perfect
  3. It deletes data
  4. It ignores numbers

Stray numbers or gaps can fool it.

Summary

  • Functions are ready-made tools: NAME(arguments).
  • SUM, AVERAGE, MIN, MAX and COUNT cover most beginner needs.
  • COUNT counts numbers; COUNTA counts anything not blank.
  • Always check the range AutoSum chooses.

Next lesson

Next: formatting numbers and making sheets easy to read.

Questions

Does AVERAGE treat blank cells as zero?

No. It ignores blanks and text and divides by the count of numbers. Type 0 if you want a zero included.

What is the difference between SUM(A1:A5) and A1+A2+A3+A4+A5?

They give the same answer, but SUM is shorter, adjusts when rows are inserted and ignores text that would cause an error with plus signs.

Can I use functions in Google Sheets?

Yes. These functions work the same way in Google Sheets.

Progress is saved in this browser.