SUM, AVERAGE, COUNT, MIN and MAX: The Everyday Functions

Excel for Jobs: Formulas, Pivot Tables and Office Skills · Formulas · 4 min read · Updated

Functions do the heavy work

  1. You select a range of cells, such as the ten amounts in G2:G11.
  2. A function such as SUM, AVERAGE, MIN or MAX works on every number in the range.
  3. It returns a single result. The total of the ten sales is 20150.
  4. For a quick check, the status bar shows the sum, average and count of selected cells with no formula.

A function is a ready-made formula with a name. Instead of writing =G2+G3+G4+... for a hundred cells, you write =SUM(G2:G101). Excel has hundreds of functions, but a small handful does most of the work in real jobs. This lesson covers the ones you will use every single day.

The shape of a function

=FUNCTIONNAME(argument1, argument2, ...)

A function starts with =, then its name, then brackets holding the arguments, the values or ranges it works on, separated by commas. When you type a function name, Excel shows a tooltip with its arguments. Use the Tab key to accept the suggested function, and always close the bracket.

SUM

SUM adds numbers. Click an empty cell below the table, say G13, and type:

=SUM(G2:G11)

With the original data the answer is 20150, the total sales for the week. You can give SUM several ranges or numbers: =SUM(G2:G5, G9). Cells containing text are ignored.

AutoSum is a shortcut. Select the cell under a column of numbers and press Alt+=. Excel guesses the range and enters SUM for you. Check that the range is right before pressing Enter.

AVERAGE

=AVERAGE(G2:G11)      -> 2015

It adds the numbers and divides by how many there are. Twenty thousand one hundred fifty divided by ten is 2015. Empty cells and text are skipped. A zero is counted, though, which can lower the average. Be careful with blanks that are really zeros.

COUNT and COUNTA

  • COUNT(range) counts cells that contain numbers. =COUNT(D2:D11) returns 10.
  • COUNTA(range) counts cells that are not empty, including text. =COUNTA(B2:B11) returns 10 because there are ten items.
  • COUNTBLANK(range) counts empty cells, which is useful for finding missing data.

Use COUNTA to count how many entries are in a list of names. Use COUNT to count numeric entries.

MIN and MAX

=MIN(G2:G11)   -> 1200   (smallest sale)
=MAX(G2:G11)   -> 2800   (largest sale)

Smallest and largest values. They also work with dates: the earliest and latest date in a column.

The status bar

When you select a range of numbers, the bar at the bottom of the window shows the average, count and sum immediately, with no formula needed. Select G2:G11 and look. It is perfect for a quick check. Right-click the status bar to add more such as Min and Max.

ROUND, ROUNDUP and ROUNDDOWN

Money often needs rounding. Use ROUND(number, digits).

=ROUND(2015.678, 2)    -> 2015.68
=ROUND(2015.678, 0)    -> 2016
=ROUND(2015.678, -1)   -> 2020   (to the nearest ten)
=ROUNDUP(2015.1, 0)    -> 2016
=ROUNDDOWN(2015.9, 0)  -> 2015

Formatting to two decimals only changes how a number looks, and the real value underneath remains long. When a result must be exactly rounded, for example prices that are paid in cash, use ROUND in the formula.

Percentages of a total

A common task is what share each item makes of the total. In H2 write =G2/$G$13 where G13 holds the total, and copy down. Format the column as a percentage. For the first row, 1200 out of 20150 is about 6%. The dollar signs keep the total fixed, as you learned in the last lesson.

Using whole columns

=SUM(G:G) adds the entire column G, so new rows are included automatically. This is convenient for sheets that keep growing, but be careful not to include your own total cell in the range, or Excel complains about a circular reference. Keep totals above the data, on a separate sheet, or use a range that stops before them.

Common errors

  • #DIV/0! means you divided by zero or by an empty cell. Check the denominator, or wrap with IFERROR (next lesson).
  • #NAME? means Excel does not recognise a name. You mistyped a function name.
  • #VALUE! means a value is the wrong type, such as text where a number is needed.
  • #REF! means a cell the formula used was deleted.
  • #### in a cell is not an error in the formula. The column is too narrow to show the number. Widen it.

Check your answer

Always sanity-check results. Do the totals look sensible? Is the average between the minimum and the maximum? Does the count equal the number of rows you can see? If a total looks odd, click the cell, press F2 and see which cells the formula includes. Most mistakes come from a range that is one row too short or too long.

Your task

  1. Add these cells below your table, with labels in column F: total with SUM, average with AVERAGE, count with COUNT, smallest and largest sale.
  2. Use AutoSum (Alt+=) to total the Qty column. The answer is 112.
  3. Add a column showing each row's share of the total, formatted as a percentage.
  4. Use ROUND to show the average to 1 decimal place.

How to check it worked

  • Total = 20150, average = 2015, count = 10, min = 1200, max = 2800 and total Qty = 112.
  • The percentage column adds up to 100%.
  • You can explain what #DIV/0! and #### mean.

Frequently asked questions

What is the difference between COUNT and COUNTA?

COUNT counts cells with numbers. COUNTA counts all cells that are not empty, including text.

Why does the cell show #### instead of a number?

The column is too narrow. Double-click the line between column letters to auto-fit the width.