Today you will
- use IF to give each row a clear status
- use SUMIF and COUNTIF to add up or count only the rows you want
- add one labelled formula to your own project workbook
Warm-up
Think of one number in your Something Real project that matters when it crosses a line, like 20 hours of Work Experience. What question would you ask the spreadsheet about it?
IF tests one condition. It gives one answer if the condition is true and a different answer if it is false.
SUMIF adds up numbers only in the rows that match a rule, such as Online sales.
COUNTIF counts how many rows match a rule, such as how many items are on target.
Example
{{code:=IF(C2>=50,"On target","Below target")}} shows On target when a tote bag sells 50 or more.
Later you can also try nested IF, IFS and AVERAGEIF.
You will build Aoife's tote-bag sheet with your teacher. Aoife sells tote bags at a local market and online. Follow the steps in the box below. The steps marked Stretch are extra, so only do them if you have time.
Type this into your new sheet:
| Item | Channel | Units sold | Price (€) | Revenue (€) | Target status |
|---|---|---|---|---|---|
| Mini tote | Online | 42 | 12 | ||
| Market tote | Market | 58 | 15 | ||
| Shopper bag | Online | 35 | 18 | ||
| Gift set | Market | 51 | 25 | ||
| Pencil case | Online | 67 | 8 |
When you finish, Online revenue should show {{code:1670}} and Items on target should show {{code:3}}.
Common snags
- The formula shows as text. Make sure the cell starts with {{code:=}} and has no space before it.
- You see {{code:#NAME?}} The function name is spelled wrong, so type it again carefully.
- SUMIF or COUNTIF shows 0. Check that the word in quotes matches the sheet exactly, with no extra spaces.
What will your formula answer?
Aoife's sheet used units sold and channel. Your project numbers may be different. Pick one of these and finish it in your head or on scrap paper.
- If Work Experience hours >= ____ , status = ____
- SUMIF for one channel or category = ____ (for example Online, materials, catering)
- COUNTIF for one status or label = ____ (for example On target, Overtime, Paid)
- Stretch: AVERAGEIF for one group = ____
Write your question in plain English first. Then put it in the cell just left of your answer, like Aoife's A8 and B8.
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.