← Back to the activity

Year 11 Computing — 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 the words D1 = pen, D2 = bag, D3 = pen, D4 = cup and D5 = pen. What number does this formula show?

    =COUNTIF(D1:D5, "pen")

  2. 2.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, ">5")
    • =COUNTIF(J1:J5, ">20")
    • =COUNTIF(J1:J5, ">=11")
    • =COUNTIF(J1:J5, "6")

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

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

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

  5. 5.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

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

  6. 6.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
    • 1
    • 4
    • 34
    • 2

  7. 7.A sheet holds C1 = 4, C2 = 15, C3 = 9 and C4 = 20. What number does this formula show?

    =SUMIF(C1:C4, ">=10")

  8. 8.A sheet holds B1 = 12, B2 = 30, B3 = 5, B4 = 22 and B5 = 30. What number does this formula show?

    =COUNTIF(B1:B5, ">20")

Answer key — Year 11 Computing — Counting and adding with a condition

  1. 1. 3
  2. 2. 1. =COUNTIF(J1:J5, ">20") 2. =COUNTIF(J1:J5, "6") 3. =COUNTIF(J1:J5, ">=11") 4. =COUNTIF(J1:J5, ">5")
  3. 3. 5
  4. 4. d) Only the cells that say pen and nothing else
  5. 5. ">10" → Three cells pass; ">15" → Two cells pass; ">=20" → Two cells pass; ">=12" → Three cells pass; "<10" → Two cells pass; "<13" → Three cells pass
  6. 6. =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
  7. 7. 35
  8. 8. 3