COUNTIF and SUMIF
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:C50 contain the word Yes?
2. Which formula counts how many scores in B2:B30 are greater than 10?
3. True or false: =COUNTIF(D2:D40,"Pass") adds up the numbers in D2:D40.
4. A table has prices in C2:C20 and the product type in B2:B20. Which formula adds up the prices of every 'Hoodie'?
5. In =SUMIF(A2:A50,"Canteen",D2:D50), which range holds the values that get added?
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:C50,"Yes") | A=SUMIF(A2:A20,"Pizza",C2:C20) | |
| 2=SUMIF(B2:B30,">10",C2:C30) | B=COUNTIF(B2:B40,">10") | |
| 3Count how many scores in B2:B40 are greater than 10 | CThe text condition Yes is missing its quote marks. It should be "Yes". | |
| 4Add up the Sales in C2:C20 for rows where column A says Pizza | DAdds up the values in C2:C30 where the matching cell in B2:B30 is greater than 10 | |
| 5=COUNTIF(C2:C50,Yes) | EThe arguments are in the wrong order. The condition must come second and the sum range third. | |
| 6=SUMIF(B2:B30,C2:C30,">10") | FCounts how many cells in C2:C50 contain the text Yes |
C
Definitions
=COUNTIF(C2:C50,"Yes")
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=_______(C2:C50,"Yes")
2.
=COUNTIF(B2:B30,"_10")
3.
=_____(A2:A20,"Food",B2:B20)
4.
=SUMIF(A2:A20,"Food",______)
5.
=COUNTIF(______,"Yes")
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
E1: =COUNTIF(C2:C50,Yes)
B
E2: =COUNTIF(C2:C50,"No")
C
E3: =E1+E2
Question 2
A
=COUNT(D2:D50,">10")
B
=COUNTIF(D2:D50,"<5")
C
=COUNTIF(D2:D50,"<=5")
Question 3
A
=SUMIF(B2:B30,"Hats",C2:C30)
B
=SUMIF(B2:B30,"Caps",C2:C30)
C
=SUMIF(B2:B30,C2:C30,"Bags")
Question 4
A
=COUNTIF(C2:C50,"Yes")
B
=COUNTIF(C2:C50,"No")
C
=COUNTIF(D2:D50,">10")
D
=SUMIF(D2:D50,">10,D2:D50)
Question 5
A
=COUNTIF(B2:B31,"Present")
B
=COUNTIF(B2:B31,"Absent"
C
=COUNTIF(B2:B31,"Late")
Answer Key
COUNTIF and SUMIF
Section A: Quick Questions
Q1
A — =COUNTIF(C2:C50,"Yes")
Q2
A — =COUNTIF(B2:B30,">10")
Q3
A — False: it counts the cells that contain Pass
Q4
B — =SUMIF(B2:B20,"Hoodie",C2:C20)
Q5
B — D2:D50
Section B: Match the Pairs
Q1
=COUNTIF(C2:C50,"Yes") → Counts how many cells in C2:C50 contain the text Yes
Q2
=SUMIF(B2:B30,">10",C2:C30) → Adds up the values in C2:C30 where the matching cell in B2:B30 is greater than 10
Q3
Count how many scores in B2:B40 are greater than 10 → =COUNTIF(B2:B40,">10")
Q4
Add up the Sales in C2:C20 for rows where column A says Pizza → =SUMIF(A2:A20,"Pizza",C2:C20)
Q5
=COUNTIF(C2:C50,Yes) → The text condition Yes is missing its quote marks. It should be "Yes".
Q6
=SUMIF(B2:B30,C2:C30,">10") → The arguments are in the wrong order. The condition must come second and the sum range third.
Section C: Definitions
Q1
=COUNTIF(C2:C50,"Yes") — COUNTIF counts the cells in a range that meet a condition. SUMIF works the same way but adds up the values that meet it. Example: count every 'Yes' in C2:C50.Section D: Fill in the Blanks
Q1
=COUNTIF(C2:C50,"Yes")Q2
=COUNTIF(B2:B30,">10")Q3
=SUMIF(A2:A20,"Food",B2:B20)Q4
=SUMIF(A2:A20,"Food",B2:B20)Q5
=COUNTIF(C2:C50,"Yes")Section E: Spot the Error
Q1
A — Text conditions in COUNTIF must be inside double quotes. It should be =COUNTIF(C2:C50,"Yes").
Q2
A — COUNT only counts numbers and does not take a condition. To count cells above 10 you need COUNTIF: =COUNTIF(D2:D50,">10").
Q3
C — SUMIF needs the range to test, then the condition, then the sum range. The condition and sum range are swapped. It should be =SUMIF(B2:B30,"Bags",C2:C30).
Q4
D — The closing quote after >10 is missing, so the condition is not a complete piece of text. It should be =SUMIF(D2:D50,">10",D2:D50).
Q5
B — The closing bracket is missing, so the formula is incomplete. It should be =COUNTIF(B2:B31,"Absent").