Today you will
- use SUM, AVERAGE, MIN, MAX and ROUND
- choose between COUNT, COUNTA and COUNTIF
- build a summary block with at least six formulas
Warm-up
Picture a column of product names beside a column of units sold. If you want to know how many products are on the list, should the spreadsheet count the names or the numbers?
SUM, AVERAGE, MIN and MAX give you the total, the average, the smallest and the largest number in a list.
COUNT counts cells that hold numbers. COUNTA counts every cell that is not empty, words too. COUNTIF counts only the cells that match a rule.
ROUND rounds a number to the decimal places you choose. You can put another function inside it.
Example
{{formula:=ROUND(AVERAGE(C2:C7), 2)}} finds the average price and rounds it to the nearest cent.
You will build a sales sheet for a market stall that sells handmade accessories. Then you will add a Summary statistics block with seven formulas. Type the table and labels from the box, and follow the steps below for your app.
| Item | Units sold | Price (€) | Revenue (€) |
|---|---|---|---|
| Shamrock keyring | 24 | 3.50 | |
| GAA crest badge | 18 | 4.00 | |
| Name stickers pack | 12 | 5.50 | |
| Phone grip | 8 | 6.00 | |
| Lanyard | 15 | 2.50 | |
| Sticker sheet | 30 | 2.00 |
Summary labels (A9 to A16):
- Summary statistics
- Total units
- Average price (€, 2 d.p.)
- Lowest units
- Highest units
- Number of products
- Products with 15+ units
- Total revenue (€)
Common issues
- My product count shows 0. You used COUNT on words, so switch to COUNTA.
- I see {{code:#NAME?}}. A function name is spelled wrong, so fix it and press {{key:Enter}} again.
- ROUND shows too many decimals. Check the number after the comma. Use 2 for money and 0 for whole numbers.
- COUNTIF gives 0. Put the rule in quotes, like {{code:">=15"}}, and point it at the number column.
Independent Practice
- Open {{code:Project_Portfolio}}, then {{code:09_specialism/sm3/}}.
- Open your project workbook, {{code:sm3_from_template}}, or another sheet of your own data.
- Below the data, type Summary statistics and a label for each figure.
- Add at least six formulas, including one nested ROUND.
- Save a copy as {{code:sm3_summary_block}}.
No project sheet yet? Open {{code:sm3_functions_practice}} and swap in 6 to 8 rows of your own data.
- {{code:sm3_summary_block}} is saved in {{code:sm3/}} with my own data.
- At least six formulas update when I change a number.
- Each count uses the right function for the column.
- Averages and money figures use ROUND.
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 - 2026. Unauthorised use, copying or distribution is not allowed.