The COUNTIF function
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula counts how many cells in C2:C30 contain the word Yes?
2. What does =COUNTIF(B2:B20,">10") do?
3. Which part of =COUNTIF(C2:C30,"Yes") is the range?
4. A class survey has Yes and No answers in D2:D25. Which formula counts the No answers?
5. True or false: =COUNTIF(A1:A10,">10") counts cells that are exactly 10.
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=COUNTIF(C2:C30,"Yes") | A=COUNTIF(A2:A30,"<50") | |
| 2=COUNTIF(B2:B20,">10") | BCounts how many cells from C2 to C30 contain the word Yes | |
| 3Count how many students answered No in cells D2 to D25 | CCounts how many cells from B2 to B20 hold a number greater than 10 | |
| 4Count how many test scores in A2:A30 are less than 50 | DThe text criteria Yes is missing quote marks. It should be "Yes". | |
| 5=COUNTIF(C2:C30,Yes) | EThe condition >10 is missing quote marks. It should be ">10". | |
| 6=COUNTIF(B2:B20,>10) | F=COUNTIF(D2:D25,"No") |
C
Definitions
=COUNTIF(range, criteria)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=_______(C2:C30,"Yes")
2.
=COUNTIF(C2_C30,"Yes")
3.
=COUNTIF(B2:B30,"_10")
4.
=COUNTIF(B2:B30,"_5")
5.
_COUNTIF(B2:B30,">=50")
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
=COUNTIF("Yes",C2:C30)
B
=COUNTIF(D2:D30,">10")
C
=COUNTIF(E2:E30,"No")
Question 2
A
=COUNTIF(B2:B20,"Yes")
B
=COUNTIF(C2:C20)
C
=COUNTIF(D2:D20,"No")
Question 3
A
A1: Pet
B
A2:A30 = Dog, Cat, Fish
C
B1: Number of dogs
D
B2: =COUNT(A2:A30,"Dog")
Question 4
A
A1: Test scores in B2:B30
B
A2: =COUNTIF(B2:B30,"=>50")
C
A3: =COUNTIF(B2:B30,"<50")
Question 5
A
A1: Goals scored
B
B2:B20 holds goal numbers
C
C1: =COUNTIF(B2:B20,">3)
Answer Key
The COUNTIF function
Section A: Quick Questions
Q1
A — =COUNTIF(C2:C30,"Yes")
Q2
A — Counts the cells in B2:B20 that are bigger than 10
Q3
B — C2:C30
Q4
A — =COUNTIF(D2:D25,"No")
Q5
B — False
Section B: Match the Pairs
Q1
=COUNTIF(C2:C30,"Yes") → Counts how many cells from C2 to C30 contain the word Yes
Q2
=COUNTIF(B2:B20,">10") → Counts how many cells from B2 to B20 hold a number greater than 10
Q3
Count how many students answered No in cells D2 to D25 → =COUNTIF(D2:D25,"No")
Q4
Count how many test scores in A2:A30 are less than 50 → =COUNTIF(A2:A30,"<50")
Q5
=COUNTIF(C2:C30,Yes) → The text criteria Yes is missing quote marks. It should be "Yes".
Q6
=COUNTIF(B2:B20,>10) → The condition >10 is missing quote marks. It should be ">10".
Section C: Definitions
Q1
=COUNTIF(range, criteria) — Counts how many cells in a range match a value or condition, such as "Yes" or ">10". Example: =COUNTIF(C2:C30,"Yes") counts every Yes in C2 to C30.Section D: Fill in the Blanks
Q1
=COUNTIF(C2:C30,"Yes")Q2
=COUNTIF(C2:C30,"Yes")Q3
=COUNTIF(B2:B30,">10")Q4
=COUNTIF(B2:B30,"<5")Q5
=COUNTIF(B2:B30,">=50")Section E: Spot the Error
Q1
A — The arguments are the wrong way around. COUNTIF needs the range first and the criteria second: =COUNTIF(C2:C30,"Yes").
Q2
B — COUNTIF needs a criteria argument as well as a range. It should be =COUNTIF(C2:C20,">10") to count the cells greater than 10.
Q3
D — COUNT only counts cells that hold numbers and does not take a condition like "Dog". To count matching text you need COUNTIF.
Q4
B — The operator is written the wrong way round. "Greater than or equal to" is >=, not =>. It should be ">=50".
Q5
C — The criteria is missing its closing quote mark. A condition such as ">3" must have quotes at both ends.