• Practice
  • About 12 min
  • 5 practice questions

Evaluating formulas on ranges with gaps: SUM, AVERAGE, COUNT, MAX and MIN

Work out the value of a formula when the range contains text, blanks and zeros, and apply the order of operations without slips.

If something looks wrong, please tell us.

The most common spreadsheet question gives a few cell values and a formula and asks for the result. Easy ones use clean numbers. Harder ones hide text, a blank or a zero in the range. This lesson trains you to read the range cell by cell before you calculate.

What each function does with text and blanks

How functions treat different cell contents
FunctionNumbersTextBlank cellZero
SUMAddedIgnoredIgnoredAdds 0
AVERAGEUsedIgnoredIgnoredUsed in the count
COUNTCountedNot countedNot countedCounted
COUNTACountedCountedNot countedCounted
MAX and MINComparedIgnoredIgnoredCompared

The key line is AVERAGE: a blank is left out, but a zero is a real number and is included, which pulls the average down.

Common slips in this question type

  • Counting the text cell as a number when finding an average.
  • Treating a blank as zero, which changes both the count and the average.
  • Forgetting that COUNTA counts text but COUNT does not.
  • Adding before multiplying when no brackets are given.

A reading routine

  • Copy the range into a small list and label each cell: number, text, blank or zero.
  • Pick out the numbers only for SUM, AVERAGE, MAX and MIN.
  • Count the numbers for AVERAGE and COUNT.
  • Apply the order of operations for any extra arithmetic: brackets, powers, multiplication and division, then addition and subtraction, each from left to right.

Worked examples

In the examples, A1 holds 12, A2 holds 8, A3 holds the text n/a, A4 holds 20, A5 is blank and A6 holds 0.

Example 1. =SUM(A1:A6) adds the numbers 12 + 8 + 20 + 0 = 40, ignoring the text and the blank. =COUNT(A1:A6) counts four numbers (12, 8, 20, 0), so it gives 4. =COUNTA(A1:A6) also counts the text, so it gives 5.

Example 2. =AVERAGE(A1:A6) = 40 / 4 = 10. The text and the blank are not counted, but the zero is. If the zero were removed, the average would be 40 / 3 = 13.33, which shows how a zero changes it.

Example 3. =MAX(A1:A6) = 20 and =MIN(A1:A6) = 0. Here the zero is a number, so it becomes the minimum.

Example 4. =A1+A2*2 gives 12 + 16 = 28, because multiplication comes first. =(A1+A2)2 gives 20 x 2 = 40. =A4/A2A1 works from left to right: 20 / 8 = 2.5 and 2.5 x 12 = 30.

Example 5. =A4-A2-A1 is worked from left to right: 20 - 8 = 12 and 12 - 12 = 0. =100/10/2 is 10 / 2 = 5, not 100 / 5 = 20, because the first division is done first.

Example 6. =SUM(A1:A2,5) adds the range and the extra number: 12 + 8 + 5 = 25. Arguments can be mixed ranges and numbers, separated by commas.

Example 7. =SUM(A1:A2)/COUNT(A1:A6) adds 12 + 8 = 20 and divides by the count of numbers in the whole range, which is 4. The result is 5. Mixing a short range in the top with a long range in the bottom is a typical exam trick, so check each range separately.

Label every cell, take the numbers only, and work operators from left to right.

Key takeaways. Text and blanks are skipped by SUM, AVERAGE, MAX and MIN. COUNT counts numbers only and COUNTA counts anything non-empty. A zero is a number. Equal-ranking operators go left to right.

Check your understanding

  1. 1. A1 to A6 hold 12, 8, the text n/a, 20, a blank and 0. What is =SUM(A1:A6)?

    1. 20
    2. 48
    3. 40
    4. 32
    Show answer

    Answer: C. Only the numbers are added: 12 + 8 + 20 + 0 = 40.

  2. 2. For the same cells, what does =COUNT(A1:A6) return?

    1. 4
    2. 6
    3. 3
    4. 5
    Show answer

    Answer: A. The numbers are 12, 8, 20 and 0, so COUNT gives 4. COUNTA would give 5 because it also counts the text.

  3. 3. For the same cells, what is =AVERAGE(A1:A6)?

    1. 13.33
    2. 6.67
    3. 8
    4. 10
    Show answer

    Answer: D. The total is 40 and four numbers are counted (the zero is included), so 40 / 4 = 10.

  4. 4. With A1 = 12 and A2 = 8, what is =A1+A2*2?

    1. 40
    2. 20
    3. 28
    4. 24
    Show answer

    Answer: C. Multiplication first: 8 x 2 = 16; then 12 + 16 = 28. Adding first would wrongly give 40.

  5. 5. What is =100/10/2?

    1. 20
    2. 5
    3. 50
    4. 0.5
    Show answer

    Answer: B. Division goes from left to right: 100 / 10 = 10, then 10 / 2 = 5.

Privacy · Terms · Refunds · Delivery