← Back to the activity

Grade 10 Computer Science — Counting and adding with a condition

Work out what COUNTIF and SUMIF show for a sheet you are given, and read the test each of them is applying.

Name: ________________________

  1. 1.A sheet holds F1 = 8, F2 = 14, F3 = 8 and F4 = 20. Match each formula to the number it shows.

    • =COUNTIF(F1:F4, "8")
    • =SUMIF(F1:F4, ">10")
    • =COUNTIF(F1:F4, ">5")
    • =SUMIF(F1:F4, "8")
    • =COUNTIF(F1:F4, ">14")
    • 16
    • 4
    • 1
    • 34
    • 2

  2. 2.A COUNTIF over a column of words is given the test "pen". Which cells does it count?

    • a) Every cell with the letters p, e and n somewhere in it
    • b) Only the cells that say pen and nothing else
    • c) Every cell that has anything at all written in it
    • d) The cells sitting in the same rows as the cells that say pen

  3. 3.A range holds the five values 5, 12, 20, 3 and 25. Sort each test by how many of those cells pass it.

    Groups: Two cells pass · Three cells pass

    • "<13"
    • ">15"
    • ">10"
    • ">=12"
    • ">=20"
    • "<10"

  4. 4.A sheet holds marks in K1:K4 — 45, 80, 62 and 90 — and the prizes those pupils won in L1:L4 — 1, 5, 2 and 8. What number does this formula show?

    =SUMIF(K1:K4, ">60", L1:L4)

  5. 5.A sheet holds J1 = 6, J2 = 18, J3 = 6, J4 = 25 and J5 = 11. Put these formulas in order of the number each one shows, smallest first.

    • =COUNTIF(J1:J5, ">=11")
    • =COUNTIF(J1:J5, ">5")
    • =COUNTIF(J1:J5, "6")
    • =COUNTIF(J1:J5, ">20")

  6. 6.A COUNTIF over the eight cells H1:H8, with the test ">50", shows 3. How many of those eight cells hold 50 or less?

  7. 7.What are the two things a COUNTIF is given?

    • a) A range of cells, and the number that the cells in it are expected to add up to
    • b) A range of cells to look at, and a test that each of those cells either passes or fails
    • c) Two ranges of cells, the second saying which of the first to count
    • d) A test, and the number of cells that ought to pass it

  8. 8.A sheet holds the words D1 = pen, D2 = bag, D3 = pen, D4 = cup and D5 = pen. What number does this formula show?

    =COUNTIF(D1:D5, "pen")

Answer key — Grade 10 Computer Science — Counting and adding with a condition

  1. 1. =COUNTIF(F1:F4, "8") → 2; =SUMIF(F1:F4, ">10") → 34; =COUNTIF(F1:F4, ">5") → 4; =SUMIF(F1:F4, "8") → 16; =COUNTIF(F1:F4, ">14") → 1
  2. 2. b) Only the cells that say pen and nothing else
  3. 3. ">10" → Three cells pass; ">15" → Two cells pass; ">=20" → Two cells pass; ">=12" → Three cells pass; "<10" → Two cells pass; "<13" → Three cells pass
  4. 4. 15
  5. 5. 1. =COUNTIF(J1:J5, ">20") 2. =COUNTIF(J1:J5, "6") 3. =COUNTIF(J1:J5, ">=11") 4. =COUNTIF(J1:J5, ">5")
  6. 6. 5
  7. 7. b) A range of cells to look at, and a test that each of those cells either passes or fails
  8. 8. 3