1 - Introduction ~5 mins
Welcome
You already know basic formulas such as SUM and AVERAGE. Today you go one step further: formulas that make decisions. Logical formulas such as IF, SUMIF and COUNTIF let a spreadsheet answer real questions about your Something Real project, for example "which items hit target?" or "what did Online sales total?" without you sorting the sheet by hand.
By the end of this lesson, you will be able to:
- Recap IF and use it to return a clear status from a condition
- Use IF alongside SUMIF and COUNTIF on the same sheet
- Add at least one labelled IF-family formula that answers a real project question
Warm-up
Think of one number in your Something Real project that only matters when it crosses a line. If your project numbers are not ready yet, borrow one of these board prompts for now and swap in your own later:
- Work Experience or placement hours: do I have 20 or more logged?
- Budget ceiling: is this cost still under my limit?
- Sales or channel total: what did Online (or one category) bring in on its own?
What question would you ask the spreadsheet about that number?
2 - Key Concepts ~5 mins
Logical formulas test a condition and return a result. Start with these three; you will meet nested IF, AVERAGEIF and IFS as stretch once the core is solid.
| Concept | Why it matters | Example |
|---|---|---|
| IF — tests one condition and returns one value if true, another if false | Turns a raw number into a clear status a reader can act on | In a market-stall sheet: {{code:=IF(C2>=50,"On target","Below target")}} flags tote bags that hit 50 sales |
| SUMIF — adds numbers only where a matching condition is met | Answers "how much from Online only?" without filtering the sheet first | {{code:=SUMIF(B2:B6,"Online",E2:E6)}} totals Online revenue for a side-hustle sales list |
| COUNTIF — counts rows that match a condition | Quickly shows how many items, days or people meet a rule | {{code:=COUNTIF(F2:F6,"On target")}} counts how many products hit target |
Stretch later: nested IF or IFS when you need three outcomes (on track / close / below); AVERAGEIF when you want the average of matching rows only. Same idea as IF and SUMIF, different job.
3 - Step-by-step Task ~15 mins
Build Aoife's tote-bag decision sheet with your teacher. You will enter the sales data, add revenue and IF-family formulas, then read the answers the sheet produces. Use this pattern on your own project next.
Must-do: data, revenue, IF status, SUMIF and COUNTIF. Stretch if time: nested IF in a new column, AVERAGEIF, optional IFS.
Worked example snapshot
Aoife runs a small tote-bag side hustle selling at a local market and online. After the core formulas, her sheet looks like this:
| Item | Channel | Units sold | Price (€) | Revenue (€) | Target status |
|---|---|---|---|---|---|
| Mini tote | Online | 42 | 12 | 504 | Below target |
| Market tote | Market | 58 | 15 | 870 | On target |
| Shopper bag | Online | 35 | 18 | 630 | Below target |
| Gift set | Market | 51 | 25 | 1275 | On target |
| Pencil case | Online | 67 | 8 | 536 | On target |
Summary answers on the same sheet: Online revenue {{code:1670}}, items on target {{code:3}}. Stretch can add a three-band status column and average units among 50+ sellers (about {{code:58.67}}).
4 - Common Issues ~3 mins
Common Issues
| Issue | Solution |
|---|---|
| The formula shows as text instead of a result | Check the cell starts with {{code:=}}. If you typed a space before the equals sign, delete it and press {{key:Enter}} again. |
| You see {{code:#NAME?}} | The function name is misspelled (for example {{code:AVERAGIF}} or {{code:SUMIFF}}). Re-type the name carefully: IF, SUMIF, COUNTIF, AVERAGEIF, IFS. |
| IF always returns the false result even when the number looks right | Check the comparison and the cell type. {{code:>=50}} needs a real number in the units column, not text that only looks like a number. Re-type the units value and try again. |
| SUMIF or COUNTIF returns 0 when you expected a total | The criteria must match the sheet spelling exactly (though text matching is not case-sensitive). If you see 0, double-check spelling, extra spaces, or that the criteria range is correct. Copy-paste the label from the data column if unsure. |
| Nested IF seems to skip the middle band | Order matters. Test the highest threshold first ({{code:>=50}}), then the next ({{code:>=40}}), then the catch-all. If you reverse the order, every large number still matches the first loose test. |
5 - From Aoife to Your Project ~4 mins
Transfer bridge
Aoife's sheet used units sold and channel. Your Something Real numbers may look different. Before you open your own workbook, pick one sentence starter and finish it in your head (or on scrap paper):
- If Work Experience / placement hours >= ____ , status = ____
- SUMIF for one channel or category = ____ (for example Online, materials, catering)
- COUNTIF status or label = ____ (for example On target, Overtime, Paid)
- AVERAGEIF for one group = ____ (stretch: average only the rows that match a rule)
Plain-English question first, formula second. On your sheet the label goes in the cell immediately left of the result, matching Aoife's A8/B8 pattern.
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 123Learn.ie 2017 - 2025. Unauthorised use, copying or distribution is not allowed.