1 - Introduction ~5 mins
Welcome
Long answer lists are hard to read. Group counts turn them into a clear picture.
By the end of this lesson, you will be able to:
- Build a summary table with COUNTIF from criteria cells
- Use AVERAGEIF, SUMIF and COUNTIFS by group
- Lock ranges with $ and check totals with COUNTA
Warm-up
How would you find how many of 24 classmates take the bus, without counting by hand?
2 - Key Concepts ~5 mins
| Concept | Why it matters | Example |
|---|---|---|
| COUNTIF: counts cells that match one criterion | Turns a long answer column into a tidy count per option | Transport equals Bus: 8 of 24 |
| Absolute reference ($): locks a range when you copy | One formula fills a whole summary table | {{code:$C$2:$C$25}} stays fixed as you drag |
AVERAGEIF, SUMIF, COUNTIFS and COUNTA appear with short cues in the guided task when you need them.
3 - Step-by-step Task: Summary Table on Sample Data ~20 mins
Paste the sample travel survey, build a Transport summary with COUNTIF, AVERAGEIF and SUMIF, add a cell-driven COUNTIFS, and check totals with COUNTA. Follow the steps for your spreadsheet app.
Sample data to paste at A1 (select the whole block, copy, then paste into {{cell:A1}}):
Response Class Transport Minutes Cost_per_week Would_cycle Journey_rating 1 TY1 Bus 25 12 Maybe 3 2 TY1 Walk 10 0 No 4 3 TY1 Car 15 0 Yes 3 4 TY1 Bus 40 15 No 2 5 TY1 Cycle 12 0 Yes 5 6 TY1 Walk 8 0 No 5 7 TY1 Train 45 25 No 2 8 TY1 Car 20 0 Maybe 3 9 TY2 Bus 30 12 Yes 2 10 TY2 Walk 15 0 Maybe 4 11 TY2 Bus 35 15 No 2 12 TY2 Car 10 0 Yes 4 13 TY2 Cycle 18 0 Yes 4 14 TY2 Bus 20 10 Maybe 3 15 TY2 Car 25 0 No 3 16 TY2 Walk 5 0 No 5 17 TY3 Bus 50 15 No 1 18 TY3 Train 40 20 Maybe 2 19 TY3 Car 12 0 Yes 4 20 TY3 Walk 20 0 Maybe 3 21 TY3 Bus 28 12 Yes 3 22 TY3 Cycle 15 0 Yes 5 23 TY3 Car 30 0 Maybe 2 24 TY3 Bus 22 10 No 3
4 - Common Issues ~3 mins
Common Issues
| Issue | Solution |
|---|---|
| Counts change wrongly when I fill down | Do not worry: this is the usual glitch. Add $ on the data range ({{code:$C$2:$C$25}}) and fill down again. |
| COUNTIF returns 0 for a label I can see | Match spelling and spaces exactly. Point criteria at the label cell. |
| Check total does not equal COUNTA | List every option once. A missing option or blank breaks the match. |
5 - Portfolio Build: 03_summary ~20 mins
Independent Practice
Must: two COUNTIF tables with $ locks (table 2 can be counts only, e.g. Class or Would_cycle). Should: AVERAGEIF or SUMIF on table 1. Must: one cell-driven COUNTIFS plus Check = COUNTA.
Scan each category column once and list every different answer under your criteria header (same idea as I2:I6). Map columns: if category is B and minutes is D, first formula is {{formula:=COUNTIF($B$2:$B$25,I2)}}.
- Sheet {{code:03_summary}} has two clear summary tables
- Formulas use locked data ranges and criteria cells
- One cell-driven COUNTIFS is on the sheet
- A check cell matches COUNTA of the answers counted
Stretch: cross-sheet ranges, e.g. {{formula:=COUNTIF(clean_data!$C$2:$C$25,I2)}}. Click the other tab first and note the exact column letter.
Unlock the full learning experience
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.