Concept
1. What is a function?
A function is a ready-made formula. Structure:
=FUNCTION_NAME(arguments)
A range is written with a colon: B2:B10 means every cell from B2 to B10. Separate cells use commas: SUM(B2, B5, B9).
2. Practice sheet
| A | B | C | |
|---|---|---|---|
| 1 | Salesperson | Region | Sales |
| 2 | Amit | North | 62000 |
| 3 | Neha | South | 48000 |
| 4 | Ravi | North | 40000 |
| 5 | Priya | West | (blank) |
| 6 | Karan | South | NA |
| 7 | Sonal | West | 55000 |
Note: C5 is blank and C6 contains the text "NA". This is intentional.
3. SUM — total
=SUM(C2:C7)
Result: 205000. SUM ignores blank cells and text.
Shortcut: in the cell below a range, press Alt + = to apply AutoSum automatically.
4. AVERAGE
=AVERAGE(C2:C7)
Result: 51250 (205000 ÷ 4). Notice it divided by 4, not 6 — the blank cell and the "NA" text were not counted.
If Priya's sales really were zero, type 0 in the cell; don't leave it blank. Zero counts in the average, a blank does not.
5. MIN and MAX
=MIN(C2:C7) → 40000
=MAX(C2:C7) → 62000
Both also ignore text and blanks.
6. The three COUNTs
| Function | What it counts | On this sheet (C2:C7) |
|---|---|---|
COUNT |
only cells containing numbers | 4 |
COUNTA |
any cell that is not empty (numbers, text, everything) | 5 |
COUNTBLANK |
only empty cells | 1 |
Easy way to remember: COUNT = numbers, COUNTA = "All" non-empty, COUNTBLANK = empty.
When to use which: how many sales entries are in numbers → COUNT. How many salespeople are on the list → COUNTA(A2:A7) = 6. How many entries are still pending → COUNTBLANK.
7. A hidden trap
If a cell contains a formula that returns "" (empty text), the cell looks empty, but:
COUNTAcounts it (because the cell contains a formula)COUNTBLANKalso counts it
So the two totals can sometimes add up to more than the range size. It's not a bug; it's how Excel behaves.
8. Status bar — answers without formulas
Select any range and look at the status bar at the bottom of Excel: Average, Count and Sum appear right there. Right-click it to turn on Min/Max as well. Perfect for quick checks.
Common mistakes
Numbers stored as text (left-aligned, with a green triangle in the corner) — SUM ignores them and the total comes out lower. Leaving a blank instead of zero, which inflates the average. And trying to count names with COUNT (the answer will be 0).