Skip to content
  • Official
  • Beginner
  • English
  • 10 steps · 2 quizzes

SUMIF, COUNTIF and IF Exercises with Answers

SUMIF, COUNTIF and IF exercises with answers: count and total rows in live sheets, use criteria and wildcards, and read nested IF as a decision tree.

  • Free
  • No account needed
  • Stop any time, pick up where you left off
Workshopy.io

WorkshopyOfficial

78 lessons · used by 2 teachers

For teachers

Teaching this? Run it live with your class.

  • Students join with a code — no accounts
  • See who’s done and who’s stuck, step by step
  • It’s your copy — change anything before class

Sign in to copy it to your workspace. It’s free.

Your path

10 steps · 2 quizzes · open any step, or read it here

  1. SUMIF, COUNTIF and IF exercises with answersStart hereIn this lesson you practise SUMIF, COUNTIF and IF on small work sheets: you count tickets, total sales by region and sort orders into bands, and each exercise is checked as you go, with the answers in hints and worked solutions.InteractiveHands-on
    Read this step

    In this lesson you practise SUMIF, COUNTIF and IF on small work sheets: you count tickets, total sales by region and sort orders into bands, and each exercise is checked as you go, with the answers in hints and worked solutions. Every formula is animated row by row, so you see which rows pass the test and which numbers get added.

    Watch the sheet below answer three questions about eight sales orders. The orange cells are the ones being tested, green rows passed, striped rows didn't.

    Interactive · Three questions, three formulas — try it in the lesson

    In this lesson you'll:

    • count rows that pass a test with COUNTIF, and write tests such as "more than 4", "not Low" and "at least the number in a cell";
    • add up only the matching rows with SUMIF, and use several conditions at once with SUMIFS and COUNTIFS;
    • build a summary table with one formula filled down, its ranges locked with $;
    • read a nested IF as a decision tree, and write the same rule with IFS and AND.

    It takes about 65 minutes. Each step ends with tasks in a live sheet: type a formula, press Enter, and the task ticks when the answer is right.

    Takeaway: COUNTIF counts the rows that pass a test, SUMIF adds them up, and IF decides what to show.

  2. COUNTIF: count the rows that pass one testA support team keeps one row per ticket.InteractiveHands-on
    Read this step

    A support team keeps one row per ticket. "How many Billing tickets are there?" is a question about rows that pass a test, and COUNTIF answers it:

    =COUNTIF(range, criteria)

    • range is the cells to test, such as the team column B2:B9;
    • criteria is the test: text in double quotes ("Billing"), a number, or a cell that holds the value (A13).

    =COUNTIF(B2:B9,"Billing") reads: in B2:B9, count the cells that say Billing. Capital letters don't matter: "billing" counts the same cells.

    Try it: do the three tasks under the sheet. Select an answer cell, type the formula in the bar above the sheet and press Enter. Each task ticks when the right count appears. In the last one, point at A13 instead of typing the team; then change A13 to Billing and watch the count follow.

    Interactive · Count tickets with COUNTIF — try it in the lesson

    What does B13 show when A13 says Accounts? Only one ticket belongs to that team.

    Takeaway: =COUNTIF(range, criteria) counts the cells in the range that pass the test; point at a cell to make the test easy to change.

  3. Criteria: more than, not equal, and a test joined to a cellThe criteria can be more than "equal to". Put a comparison in front of the value, and keep the whole test inside double quotesInteractiveHands-on
    Read this step

    The criteria can be more than "equal to". Put a comparison in front of the value, and keep the whole test inside double quotes:

    Criteria Counts the cells that are…
    ">4" more than 4
    ">=4" 4 or more
    "<5" less than 5
    "<>Low" anything but Low (blank cells too)
    "" empty
    ">="&B13 at least the number in B13

    The last row is the one people get wrong. Inside quotes, ">=B13" is just text, so Excel compares the hours with the letters B13 and finds nothing. To use a cell, close the quotes and join the comparison to the cell with &: ">="&B13.

    Try it: do the three tasks. In the last one, change B13 to another number and watch B14 recount.

    Interactive · Comparisons and & in the criteria — try it in the lesson

    Set B13 to 6: how many tickets are left? Then try 9.

    Takeaway: comparisons go inside the quotes (">4", "<>Low"); a cell goes outside, joined with & (">="&B13).

  4. Wildcards: match part of the text with * and ?Names and codes are rarely typed the same way twice.InteractiveHands-on
    Read this step

    Names and codes are rarely typed the same way twice. Two wildcards in a text criteria let COUNTIF match part of a cell:

    • * stands for any number of characters, none included: "*Ltd" matches every name that ends in Ltd, "EU-*" every code that starts with EU-, "*harbor*" every name with harbor anywhere in it.
    • ? stands for exactly one character: "EU-??" matches EU-31 but not EU-7 or EU-104.
    • To look for a real * or ?, put a tilde in front: "~*".

    Wildcards work in COUNTIF, SUMIF, COUNTIFS, SUMIFS and VLOOKUP, and only on text. Capital letters still don't matter.

    Try it: a customer list with country codes. Do the three tasks and watch which rows the pattern keeps.

    Interactive · Wildcards in COUNTIF — try it in the lesson

    Which pattern finds every customer with "Inc" anywhere in the name? Try it in an answer cell.

    Takeaway: * is any run of characters, ? is exactly one; both work only inside a text criteria.

  5. SUMIF: add up only the rows that matchCOUNTIF tells you how many. SUMIF tells you how much: it tests one range and adds the matching cells of another. =SUMIF(range, criteria, [sumrange]) · range: the cells to test, such as the regions in B2:B9; criteria: the test, written exactly as in COUNTIF;InteractiveHands-on
    Read this step

    COUNTIF tells you how many. SUMIF tells you how much: it tests one range and adds the matching cells of another.

    =SUMIF(range, criteria, [sum_range])

    • range: the cells to test, such as the regions in B2:B9;
    • criteria: the test, written exactly as in COUNTIF;
    • sum_range: the numbers to add, such as the amounts in D2:D9. It is optional: leave it out and SUMIF adds the tested cells themselves, so =SUMIF(D2:D9,"<500") adds every amount under 500.

    =SUMIF(B2:B9,"North",D2:D9) reads: where B2:B9 says North, add the amount from the same row of D2:D9. The coloured line under the sheet labels each part of your formula as you type it.

    Try it: do the three tasks, and watch the matching amounts light up before they're added.

    Interactive · Total sales with SUMIF — try it in the lesson

    Change A12 to Chair. Which two amounts does SUMIF add now?

    Takeaway: =SUMIF(test range, criteria, sum range): the range you test comes first, the numbers you add come last.

  6. Quick check: COUNTIF, criteria and SUMIFFour questions on the last four steps. B2:B9 holds regions, C2:C9 products, D2:D9 amounts.Quiz · 4 questions
    Read the questions

    Four questions on the last four steps. B2:B9 holds regions, C2:C9 products, D2:D9 amounts.

    Question 1. What does =COUNTIF(B2:B9,"North") return?

    • The total of the North amounts
    • How many cells in B2:B9 say North
    • TRUE if any cell says North
    • The first row that says North

    Question 2. Which formula adds the North amounts?

    • =SUMIF(B2:B9,"North",D2:D9)
    • =SUMIF(D2:D9,"North",B2:B9)
    • =SUMIF("North",B2:B9,D2:D9)
    • =COUNTIF(B2:B9,"North",D2:D9)

    Question 3. F1 holds a target. Which criteria counts the amounts above it?

    • ">F1"
    • ">"&F1
    • >F1
    • "F1>"

    Question 4. Which code does "EU-??" match?

    • EU-7
    • EU-31
    • EU-104
    • US-31
  7. SUMIFS and COUNTIFS: more than one condition"North laptops" is two tests on the same row.InteractiveHands-on
    Read this step

    "North laptops" is two tests on the same row. SUMIFS and COUNTIFS take as many pairs of range and criteria as you need, and keep only the rows that pass every one:

    =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

    =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …)

    Watch the order: SUMIFS puts the sum range first, SUMIF puts it last. It is the most common slip with these functions, and the coloured line under the sheet shows which part is which. A range can be tested and summed at once: =SUMIFS(D2:D9,D2:D9,">=1000") adds the amounts of 1000 or more.

    In the animation, a cell that passed its own test but sits in a row that failed another test is shaded lightly: one test isn't enough.

    Try it: do the three tasks. Every range in one SUMIFS must have the same number of rows.

    Interactive · Two conditions with SUMIFS and COUNTIFS — try it in the lesson

    What would =COUNTIFS(B2:B9,"North",B2:B9,"South") return? Think first, then try it in an answer cell.

    Takeaway: SUMIFS and COUNTIFS keep the rows that pass every test; in SUMIFS the sum range comes first.

  8. A summary table: one SUMIF, filled down, with $A manager wants sales and order counts per region. You don't need a formula per region: write the first one, point its criteria at the region's name, and fill it down. When you fill a formula down, every reference without $ moves one row per row.InteractiveHands-on
    Read this step

    A manager wants sales and order counts per region. You don't need a formula per region: write the first one, point its criteria at the region's name, and fill it down.

    When you fill a formula down, every reference without $ moves one row per row. That's what you want for the region (A11, then A12, then A13) and exactly what you don't want for the data: B2:B9 would become B3:B10, then B4:B11, and the rows at the top drop out of the test. Lock the data with $:

    =SUMIF($B$2:$B$9,A11,$D$2:$D$9)

    In Excel, press F4 while the cursor is in a reference to add the dollars.

    Try it: write B11, press Fill down, and check the totals. Turn on Show formulas to see what each copied formula refers to. If a total is wrong, fix B11 and fill again.

    Interactive · A summary table filled down — try it in the lesson

    Try it without the dollars first: which region comes out wrong, and why that one?

    Takeaway: in a formula you fill down, lock the data ranges with $ and leave the criteria cell free to move.

  9. Nested IF: a decision tree in one formulaAn IF chooses between two answers. To choose between three, put a second IF where the first one's "if false" answer goes: =IF(D2>=1000,"Gold",IF(D2>=500,"Silver","Bronze")) · Read it as a decision tree.InteractiveHands-on
    Read this step

    An IF chooses between two answers. To choose between three, put a second IF where the first one's "if false" answer goes:

    =IF(D2>=1000,"Gold",IF(D2>=500,"Silver","Bronze"))

    Read it as a decision tree. Excel asks the first question; if the answer is TRUE it stops there and shows Gold. Only when it is FALSE does it go on to the second IF. So the order of the tests matters: ask the strictest question first. Test >=500 first and every order of 1000 or more stops at Silver; Gold can never be reached.

    Try it: move the amount and watch the order travel through the tree. Do the three tasks, then switch the tests round.

    Interactive · A nested IF as a decision tree — try it in the lesson

    At 499 the order is Bronze, at 500 Silver. Which test decides that, and what would > instead of >= change?

    Takeaway: a nested IF stops at the first TRUE test, so put the strictest test first.

  10. IFS and AND: the same rule, easier to readThree bands make two IFs; five bands make four, and the brackets get hard to count.InteractiveHands-on
    Read this step

    Three bands make two IFs; five bands make four, and the brackets get hard to count. Excel 2019 and later, Excel for Microsoft 365 and Google Sheets have IFS, which lists the tests in order:

    =IFS(C2>=1000,"Gold",C2>=500,"Silver",TRUE,"Bronze")

    IFS returns the answer of the first test that is TRUE, so the order matters just as in the tree. If no test is TRUE it shows #N/A; the last pair TRUE,"Bronze" is the catch-all, the "everything else".

    To ask two things at once, put AND (every test TRUE) or OR (at least one TRUE) inside the IF:

    =IF(AND(B2="North",C2>=1000),"Bonus","No")

    Try it: do the four tasks. The bands you fill in column D feed the COUNTIF in task 3.

    Interactive · IFS bands, then COUNTIF and AND — try it in the lesson

    Change B12 to use OR instead of AND. Does row 2 still get a bonus? Would row 3 (South, 300)?

    Takeaway: IFS lists the tests in order and ends with TRUE for everything else; AND and OR put two tests in one.

Comments

You’ll sign in with Google before it’s posted.Comments are public — don’t name your students.
0 / 1,500

No comments yet. Be the first to say how it went.

Using it

Workshopy shares this lesson under CC BY 4.0. Anyone may use and change it, with credit to you. Anyone can learn it here, free. A copy in your workspace is yours to change and run live; your students never need an account.

Versions

  1. 1

    Version 1Current

    · First published

Copies are made from the current version. A copy you already have doesn’t change.

More from Workshopy

All 78 lessons
All 21 Data & AI lessons