Theory
Three counting questions, three answers
Meera asks Aryan three quick questions about the shop chain sheet:
1. How many items have a price entered?
2. How many rows have any data at all?
3. What is the total sales of Tea only?
Aryan knows SUM and AVERAGE from BCA105, but these need sharper tools. Question 1 and 2 look identical yet need different functions, and getting them mixed up gives wrong counts.
This lesson goes past basic SUM into the counting family and the conditional -IF family, the functions that answer real business questions.
At a glance
The counting family (the tricky part)
| Function | Counts | Example answer |
|---|---|---|
| COUNT(range) | Cells with NUMBERS only | Items with a price = 48 |
| COUNTA(range) | Any NON-BLANK cell (text too) | Rows with data = 50 |
| COUNTBLANK(range) | EMPTY cells | Missing prices = 2 |
| COUNTIF(range, crit) | Cells matching a condition | Rows saying 'Tea' = 6 |
Theory
COUNT vs COUNTA: the classic mix-up
Questions 1 and 2 differ by one word:
- COUNT counts only cells holding numbers.
=COUNT(C2:C51)answers 'how many items have a price' (skips blanks and text). - COUNTA counts any non-blank cell, numbers or text.
=COUNTA(A2:A51)answers 'how many rows have any data' (item names are text, so COUNT would miss them). - COUNTBLANK counts the empty cells, useful for spotting missing data.
So Aryan uses COUNT for prices and COUNTA for the name column. Same-looking questions, different functions, this exact pair is a guaranteed exam trap (you met it in BCA105 SQL as COUNT(*) vs COUNT(col)).
Theory
The conditional -IF family
For 'Tea only', you need a condition. Add IF to the function name:
- COUNTIF(range, criteria):
=COUNTIF(A2:A51, "Tea")counts Tea rows. - SUMIF(range, criteria, sum_range):
=SUMIF(A2:A51, "Tea", D2:D51)totals Tea's sales (from BCA105). - AVERAGEIF: averages only matching rows.
- COUNTIFS / SUMIFS: multiple conditions at once ('Tea' and price > 100).
When you copy these down, remember Unit 2's $ lock: anchor the range and criteria so they do not drift. Conditional aggregates are the daily bread of real reporting.
Quiz
A column has 50 rows: 48 hold numbers, 2 are blank. What do COUNT and COUNTA return?
- COUNT = 48 (numbers only), COUNTA = 48 (non-blank cells)
- Both return 50
- COUNT = 50, COUNTA = 48
- Both return 48 only if the blanks are deleted
Show the answer
COUNT = 48 (numbers only), COUNTA = 48 (non-blank cells)
COUNT counts the 48 number cells (skipping the 2 blanks). COUNTA counts non-blank cells, which are also the 48 filled ones (the 2 blanks are excluded). They match HERE because all filled cells are numbers. They would DIFFER if some cells held text: COUNT would skip text, COUNTA would include it. That number-vs-anything distinction is the whole point of the pair.
Think first
Which function for 'how many items are out of stock'?
Aryan's stock column has quantities, and out-of-stock items are left BLANK. To count how many items are out of stock, which function fits best, and why not COUNT?
Show the answer
COUNTBLANK(stock_range), it counts the empty cells, which are exactly the out-of-stock items. COUNT would count the filled (in-stock) cells, the opposite of what he wants. (Alternatively, COUNTIF(range, "") also counts blanks.) Choosing the counting function that matches what 'empty' means in the data is the real skill; blanks carry meaning here, so COUNTBLANK is the tool.
Watch out
Where marks leak
Confusing COUNT (numbers only) with COUNTA (any non-blank, text included), and forgetting COUNTBLANK for empties. Getting SUMIF's argument order wrong (range, criteria, sum_range). Forgetting to $-lock the range/criteria when copying an -IF formula (Unit 2 drift bug). And text criteria need quotes ("Tea"). These counting and conditional distinctions are dense with easy elective marks.
Theory
These become PivotTables later
COUNTIF and SUMIF answer 'per category' questions one formula at a time; a PivotTable (coming later this unit) answers all of them at once, interactively. Learning the functions first makes pivots feel like magic you understand. Next lesson deepens decision-making: logical functions, IF, AND, OR, NOT, plus nested IF for multi-level grading, going beyond BCA105's single IF.
Summary
Key takeaways
- SUM, AVERAGE, MAX, MIN are the core aggregates (from BCA105).
- COUNT counts NUMBERS only; COUNTA counts any NON-BLANK cell (text too); COUNTBLANK counts empties.
- COUNTIF / SUMIF / AVERAGEIF add a condition (range, criteria[, sum_range]).
- COUNTIFS / SUMIFS handle multiple conditions at once.
- Lock the range and criteria with $ when copying conditional formulas.
- Memory hook: COUNT = numbers, COUNTA = anything, COUNTBLANK = empties, add IF for a condition.