Computer Skills

Intermediate

60 mins

Teacher/Student led

+70 XP

What you need:
Chromebook/Laptop/PC or iPad/Tablet
IWB/Projector/Large Screen

Logical Formulas with Standard Functions: Recap and Extend

Learn to make your spreadsheet decide and filter data. You'll combine IF with SUM, COUNT, and AVERAGE to answer real questions about your project using SUMIF, COUNTIF, and AVERAGEIF.

Warning: Your screen size is quite small for viewing this. Rotate your device into landscape mode if you can.

1 Introduction
2 Key Concepts
3 Step-by-step Task
4 Common Issues
5 Portfolio Build
6 Lesson Summary

1 - Introduction ~5 mins

Welcome

You already know how to add up numbers with SUM and find an average. Today you'll teach your spreadsheet to make decisions and to count and total only the rows that matter. These logical formulas are what turn a plain list of project data into a sheet that answers real questions about your Something Real project.

By the end of this lesson, you will be able to:

Recap IF and extend it with nested IF and IFS
Combine IF logic with SUM, COUNT, and AVERAGE using SUMIF, COUNTIF, and AVERAGEIF
Use an AI assistant as a formula explainer while you write the formulas yourself

Warm-up

Think of one yes/no question your project data could answer. For example: "Is each item under budget?" or "Did I hit my target this week?" How would you decide the answer if you were checking it by hand, one row at a time?

👇 Mark your steps as done as you complete them.

+5 XP

2 - Key Concepts ~8 mins

These are the logical functions you'll use today. Each one lets your sheet apply a rule instead of just recording a number.

Concept	Why it matters	Example
IF — checks one condition and returns one of two results: `=IF(test, value_if_true, value_if_false)`	Lets your sheet flag rows automatically instead of you eyeballing every row	In a project budget, `=IF(D2>50,"Over","OK")` labels each cost as Over or OK
Nested IF / IFS — chains more than two outcomes; IFS is a cleaner way to write the same logic in newer versions	Real decisions often have more than two answers (low / medium / high)	`=IFS(D2>100,"High",D2>50,"Medium",TRUE,"Low")` bands your event's match-day costs
SUMIF — adds up only the cells that meet a condition	Totals just one category of your project data without sorting first	`=SUMIF(A2:A6,"Equipment",D2:D6)` totals only the equipment costs
COUNTIF — counts how many cells meet a condition	Answers "how many of these happened?" in one formula	`=COUNTIF(D2:D6,">50")` counts how many costs are over €50
AVERAGEIF — averages only the cells that meet a condition	Finds the average of one group while ignoring the rest	`=AVERAGEIF(A2:A6,"Travel",D2:D6)` gives the average travel cost
AI as Explainer — asking an AI "explain this formula at a level I can teach back" while you still type it yourself	You keep ownership of the work; AI helps you understand, not replace you	—

Owning your AI use

Using AI as an Explainer is fine and useful. Ask it to explain what a formula does or why it gave the wrong answer, then write and check the formula yourself. If AI helped, add a one-line note to your file saying what you used it for.

👇 Mark your steps as done as you complete them.

+10 XP

3 - Step-by-step Task ~20 mins

You'll build a small worked example: a five-item project cost sheet, then add an IF flag, a SUMIF total, a COUNTIF count, and an AVERAGEIF average. This mirrors exactly what you'll then do with your own project data.

+15 XP

4 - Common Issues ~5 mins

Common Issues

Issue	Solution
My SUMIF or AVERAGEIF returns 0 or an error	Check the category text in your condition matches the spelling in the range exactly, including capital letters
COUNTIF gives the wrong number	Put the condition in quotes with the operator inside, like {{formula:=COUNTIF(D2:D6,">50")}}, and check your range covers all the rows
My IF shows the formula text instead of a result	Make sure the cell starts with {{formula:=}} and is not formatted as text — clear formatting and re-enter
Autofill changed a range I wanted to stay fixed	You'll fix this properly with absolute references later — for now, retype the formula in the cell it went wrong in

+10 XP

5 - Portfolio Build ~17 mins

Independent Practice

Your goal: Add an IF-family formula to your own project spreadsheet so it answers a real yes/no or category question about your Something Real project on its own.

Time: ~20 minutes

Task: Open your {{code:Digital_Portfolio}} folder in your cloud storage and create a new spreadsheet called {{code:69_logical_formulas}}. Copy in the project cost data you have been building for your Something Real project, then add at least one IF-family formula (IF, IFS, SUMIF, COUNTIF, or AVERAGEIF) that answers a genuine question about that data, using the worked example as your model. If you use AI to explain a formula, write the formula yourself and add a one-line note in the sheet saying what you asked the AI.

Success criteria:

At least one logical formula gives a correct result you can check by hand
The formula answers a real question about your project, not a made-up one
Any AI help is disclosed in a short note in the sheet
The spreadsheet is saved as {{code:69_logical_formulas}} in your {{code:Digital_Portfolio}} folder

+15 XP

123learn · Online learning platform

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.

Hundreds of curriculum-aligned lessons

Interactive activities in every lesson

Printable resources & progress tracking

Get started

Copyright Notice
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.