SUM, COUNT and AVERAGE without embarrassing yourself

Bisi asks a simple question: "What's our average sale?"

Her analyst answers ₦11,755. Her accountant answers ₦20,306. Neither has made a mistake.

The analyst averaged the 938 lines in the file. The accountant averaged the 543 receipts, because a customer who buys rice, oil and milk together made one sale, not three.

Both numbers are correct arithmetic. Only one of them answers the question Bisi actually asked, and neither analyst knew there was a question to ask.

This lesson is about the four functions everyone thinks they already know, and the four ways they quietly mislead.

Words in this lesson

Your data from here on

Everything in this module uses the clean table you built in Module 2: 938 rows, January to March 2026, totalling ₦11,026,300. If your numbers don't match, go back and re-run the checks from lesson 2.5 before continuing, because every answer below depends on that table being right.

Assume your table is named Sales, with columns sale_date, receipt_no, branch, customer_name, item, category, qty, unit_price_ngn and amount_ngn.

The four functions

Type those five. They take a minute, and they are the backbone of everything that follows.

Trap 1: SUM silently ignores text

From lesson 1.1: Microsoft's documentation says Excel's SUM ignores text values. Google's help says COUNT counts only numeric values; text values are ignored.

So if 40 of your amounts are still text, SUM skips them and reports a smaller number with no warning at all.

The test: COUNT the amount column. If it doesn't equal your row count, some values aren't numbers.

=COUNT(Sales[amount_ngn])     → 938
=COUNTA(Sales[amount_ngn])    → 938

When those two disagree, the gap is the number of non-numeric values, and your total is wrong. This is the same COUNT-versus-COUNTA check from lesson 2.1, and it is worth running every single time before you trust a total.

Trap 2: AVERAGE ignores text, but counts zeros

Google's help centre is explicit: AVERAGE returns the numerical average value in a dataset, ignoring text. Text isn't treated as zero; it is left out of both the total and the count. Google notes that to have text values counted as 0 instead, you use AVERAGEA.

That distinction matters more than it sounds:

So before averaging, ask: should the blanks be blanks, or should they be zeros? For sales, an empty row usually means "no data", and a 0 means "sold nothing". They are different facts, and the average changes depending on which one is in your cells.

Trap 3: the unit of analysis

This is the trap from the opening, and it is the one that embarrasses people in meetings.

Before you calculate any average, say out loud what one row of the answer represents. If you can't, you don't yet know what you're computing.

To average by receipt, you need a total per receipt first, which is what pivot tables do in Module 5. For now, the habit is the point: state the unit before you calculate.

Trap 4: the average that describes nobody

Bisi's average receipt is ₦20,306. Her median receipt, the middle one when they're lined up in order, is ₦10,200.

Half her receipts are under ₦10,200. The average is nearly double that, because a few large receipts, the biggest being ₦159,600, pull it upward.

At line level the gap is wider still:

The typical line on a Bisi receipt is a ₦4,950 item. The mean says ₦11,755, because a 50kg bag of rice at ₦86,500 counts the same as 17 packets of noodles when you average them.

Google's help notes that AVERAGE returns the mean, and points you to MEDIAN for the median. Report the average alone here and Bisi will stock her shop for a typical customer who doesn't exist.

=AVERAGE(range)    → the mean
=MEDIAN(range)     → the middle value

When the mean and the median are far apart, say both. Module 4 explains why in depth; the habit starts now.

Reading a formula out loud

If a formula confuses you, read it as a sentence:

=SUM(Sales[amount_ngn]) → "add up the amount column of the Sales table"

=AVERAGE(Sales[qty]) → "the mean of the qty column"

If you can't say it as a sentence, you don't know what it does yet, and you certainly can't defend it when someone asks.

In Excel

All four functions are identical: SUM, COUNT, COUNTA, AVERAGE, MEDIAN. Structured references work the same way once your data is a table (lesson 2.5). Excel also has AVERAGEA, matching Google's.

Common mistakes

The short version

• Google's help: COUNT counts only numeric values, and AVERAGE ignores text (use AVERAGEA to treat text as 0).
• Check COUNT against your row count before trusting any total. Bisi: 938 and 938.
• State the unit of analysis first. Bisi's average line is ₦11,755; her average receipt is ₦20,306.
• When the mean and median are far apart, report both. Bisi receipts: ₦20,306 against ₦10,200. Bisi lines: ₦11,755 against ₦4,950.
• Bisi January–March: ₦11,026,300 across 938 lines, 543 receipts and 1,990 units.

Try it yourself

1. In your analysis sheet, calculate total revenue, line count, unit count and average line value. Compare with the numbers above.
2. Run =COUNT() and =COUNTA() on the amount column. Explain in one sentence why they match.
3. Use =COUNTA(UNIQUE(Sales[receipt_no])) to count receipts. You should get 543.
4. Calculate revenue ÷ receipts by hand. You should get about ₦20,306.
5. Calculate =MEDIAN(Sales[amount_ngn]). You should get ₦4,950 against a mean of ₦11,755. Write one sentence on what that gap tells you about Bisi's sales.
6. Deliberately break it: type the word "nil" into one amount cell in a copy of your sheet. Note what happens to SUM, COUNT, COUNTA and AVERAGE. Undo it.
7. Write, in one sentence each, what "average sale" could mean to a shop owner, and which number you'd give her.

You'll know you've got it when someone asks for "the average sale" and your first response is a question about what they mean by a sale, not a number.