Computer Skills
Intermediate
60 mins
Teacher/Student led
Learning Goals
Learning Outcomes
+65 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.

Teacher Class Feed

Load previous activity

    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.

    ConceptWhy it mattersExample
    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 rowIn 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 versionsReal 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 conditionTotals 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 conditionAnswers "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 conditionFinds 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 yourselfYou 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

    IssueSolution
    My SUMIF or AVERAGEIF returns 0 or an errorCheck the category text in your condition matches the spelling in the range exactly, including capital letters
    COUNTIF gives the wrong numberPut 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 resultMake 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 fixedYou'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
    DigitalSkills.org · 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 DigitalSkills.org 2017 - 2025. Unauthorised use, copying or distribution is not allowed.
    🍪 Our website uses cookies to make your browsing experience better. By using our website you agree to our use of cookies. Learn more