Theory
Midnight tallying, gone
Remember Meera adding her sales column by hand at midnight? Watch this. She clicks an empty cell, types =SUM(B2:B20), presses Enter, and the total for all twenty items appears instantly. Change any price and it re-totals itself.
That is why the spreadsheet exists. A function is a ready-made calculator you point at a range of cells. This lesson gives Meera the essential seven, and the one that separates beginners from real users: SUMIF.
Theory
A calculator with a built-in team
A plain calculator adds numbers you type one by one. An Excel function is a calculator with a team: you say "SUM everything from B2 to B20" and it grabs all nineteen values and adds them itself. You never retype the numbers, you point at where they live. Change a value in the range and the team recounts instantly. That is the leap from calculator to spreadsheet.
Theory
The grammar of a formula
Every formula follows the same grammar:
=FUNCTION(range)
- It always starts with `=` (that is how Excel knows it is a formula, not text).
- The range uses a colon:
B2:B20means "B2 through B20", all cells in between.
So =SUM(B2:B20) reads: "equals, add up, everything from B2 through B20". Master this one pattern and every function below is just a different word in the same slot.
At a glance
The essential functions
| Function | What it returns | Meera's use |
|---|---|---|
| SUM(B2:B20) | Total of the range | Day's total sales |
| AVERAGE(B2:B20) | The mean | Average sale per item |
| COUNT(B2:B20) | How many NUMBERS | Items with a price entered |
| MAX / MIN | Largest / smallest | Best and worst seller |
| SUMIF(A:A,"Tea",B:B) | Conditional total | Total of just Tea rows |
| PMT / STDEV | EMI / spread | Loan instalment; sales variation |
Theory
SUMIF: adding with a condition
SUM adds everything. But Meera often wants "total sales of Tea only". That needs a condition, and SUMIF has three parts:
=SUMIF(range, criteria, sum_range)
=SUMIF(A2:A20, "Tea", B2:B20) reads: "look in A2:A20, wherever it says Tea, add the matching value from B2:B20".
Three slots: where to look, the condition, what to add. This selective adding is the workhorse of every inventory, expense and sales report.
Quiz
Meera's column B has 20 cells: 18 hold numbers, 2 are blank. What does =COUNT(B2:B21) return?
- 18, COUNT counts only cells containing numbers
- 20, COUNT counts every cell
- 2, COUNT counts the blanks
- 0, COUNT needs text
Show the answer
18, COUNT counts only cells containing numbers
COUNT counts only cells that contain numbers, so the 2 blanks are skipped: 18. If she wanted all non-empty cells (including text labels), that is COUNTA. This COUNT-vs-COUNTA distinction is a favourite exam trap: COUNT = numbers only, COUNTA = anything non-blank.
Think first
Read a SUMIF
Meera writes =SUMIF(A2:A20, "Sugar", C2:C20) where column A is item names and column C is quantity sold. In plain words, what total does this give her?
Show the answer
"Look through the item names in A2:A20; every row that says Sugar, add up its quantity from C2:C20." So it gives the total quantity of Sugar sold. The three slots decode as: where to look (A), what to match (Sugar), what to add (C). Once you can read a SUMIF aloud like a sentence, you can write any conditional total.
Watch out
Where marks leak
Forgetting the leading `=`, without it Excel stores your formula as plain text and computes nothing. Thinking COUNT counts everything, it counts numbers only (use COUNTA for text). Getting SUMIF's three arguments out of order, it is range, criteria, sum_range. And a range needs a colon (B2:B20), not a dash. These small syntax slips are exactly what practical exams penalise.
Theory
You are doing statistics already
AVERAGE is the mean, STDEV is the standard deviation, the exact tools you will meet formally in BCA302 (Statistical Methods). And PMT computing an EMI is real financial maths. Excel lets you use these before you derive them, which is a gift: you build intuition now, and the theory later just names what you already feel. Next: the logical formulas, IF, AND, OR, that let a sheet make decisions.
Summary
Key takeaways
- Every formula starts with = and points at a range (B2:B20, colon = through).
- SUM adds; AVERAGE (mean), COUNT (numbers only), MAX/MIN summarise a range.
- COUNT counts numbers; COUNTA counts any non-blank cell.
- SUMIF(range, criteria, sum_range) adds only cells meeting a condition: where, what, add.
- PMT computes an EMI; STDEV computes spread (standard deviation).
- Memory hook: a function is a calculator with a team, you point at the range.