COUNTIFS and SUMIFS with several criteria
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula counts students in Year 9 who scored over 50?
2. A row is counted by COUNTIFS with two criteria pairs when...
3. Which argument comes FIRST in SUMIFS?
4. Which formula adds the Qty (column D) for Tech items (column B) with Price (column C) over 10?
5. Which formula has a mistake?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=COUNTIFS(A:A,"Year 9",B:B,">50") | AThe criteria range and criterion pairs are mixed up: "Year 9" has no range before it and C2:C9 is used as a criterion. Fix it as =SUMIFS(C2:C9,A2:A9,"Year 9",B2:B9,">50") | |
| 2=SUMIFS(C:C,A:A,"Year 9",B:B,">50") | BCounts the rows where column A is Year 9 AND column B is greater than 50 | |
| 3COUNTIFS needs a criteria range followed by its criterion, repeated for each condition | CThe two criteria ranges are different sizes (A2:A9 and B2:B8). Fix it as =COUNTIFS(A2:A9,"Year 9",B2:B9,">50") | |
| 4In SUMIFS the range of numbers to add comes first, before the criteria pairs | DAdds up column C only for rows where column A is Year 9 AND column B is greater than 50 | |
| 5=SUMIFS(A2:A9,"Year 9",C2:C9,B2:B9,">50") | E=COUNTIFS(B2:B9,">=50",C2:C9,"<80") | |
| 6=COUNTIFS(A2:A9,"Year 9",B2:B8,">50") | F=SUMIFS(D2:D9,B2:B9,"Tech",D2:D9,">10") |
C
Definitions
COUNTIFS
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=________(A:A,"Year 9",B:B,">50")
2.
=COUNTIFS(A:A,"Year 9",B:B,"_50")
3.
=______(C:C,A:A,"Year 9",B:B,">50")
4.
=SUMIFS(___,A:A,"Year 9",B:B,">50")
5.
=SUMIFS(D2:D9,B2:B9,"Tech",_____,">10")
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
Count Y9 students over 50
B
=COUNTIFS(A:A,"Y9",">50")
C
Result is the number of rows
Question 2
A
A=year, B=score, C=points
B
F1 holds ">50"; add points
C
=SUMIFS(A:A,"Y9",B:B,F1,C:C)
Question 3
A
A=house, B=points
B
Count Red with points above 90
C
=COUNTIFS(A:A,"Red",B:B,>90)
Question 4
A
Count Y9 rows scoring over 80
B
=COUNTIF(A:A,"Y9",B:B,">80")
C
Both tests must be true
Question 5
A
A=item B=category C=price
B
D=qty and F1 holds "<20"
C
Sum qty of cheap Tech items
D
=SUMIFS(D:D,B:B,"Tech",C:C)
Answer Key
COUNTIFS and SUMIFS with several criteria
Section A: Quick Questions
Q1
A — =COUNTIFS(A:A,"Year 9",B:B,">50")
Q2
B — It meets BOTH criteria
Q3
A — sum_range (the numbers to add)
Q4
A — =SUMIFS(D2:D9,B2:B9,"Tech",C2:C9,">10")
Q5
B — =COUNTIFS(A2:A9,"Year 9",B2:B8,">50")
Section B: Match the Pairs
Q1
=COUNTIFS(A:A,"Year 9",B:B,">50") → Counts the rows where column A is Year 9 AND column B is greater than 50
Q2
=SUMIFS(C:C,A:A,"Year 9",B:B,">50") → Adds up column C only for rows where column A is Year 9 AND column B is greater than 50
Q3
COUNTIFS needs a criteria range followed by its criterion, repeated for each condition → =COUNTIFS(B2:B9,">=50",C2:C9,"<80")
Q4
In SUMIFS the range of numbers to add comes first, before the criteria pairs → =SUMIFS(D2:D9,B2:B9,"Tech",D2:D9,">10")
Q5
=SUMIFS(A2:A9,"Year 9",C2:C9,B2:B9,">50") → The criteria range and criterion pairs are mixed up: "Year 9" has no range before it and C2:C9 is used as a criterion. Fix it as =SUMIFS(C2:C9,A2:A9,"Year 9",B2:B9,">50")
Q6
=COUNTIFS(A2:A9,"Year 9",B2:B8,">50") → The two criteria ranges are different sizes (A2:A9 and B2:B8). Fix it as =COUNTIFS(A2:A9,"Year 9",B2:B9,">50")
Section C: Definitions
Q1
COUNTIFS — Counts only rows that meet two or more criteria at once, using pairs of criteria range and criterion. SUMIFS works the same way but adds numbers, and its sum_range comes first.Section D: Fill in the Blanks
Q1
=COUNTIFS(A:A,"Year 9",B:B,">50")Q2
=COUNTIFS(A:A,"Year 9",B:B,">50")Q3
=SUMIFS(C:C,A:A,"Year 9",B:B,">50")Q4
=SUMIFS(C:C,A:A,"Year 9",B:B,">50")Q5
=SUMIFS(D2:D9,B2:B9,"Tech",C2:C9,">10")Section E: Spot the Error
Q1
B — The second criterion has no criteria range in front of it. COUNTIFS needs pairs of range and criterion, so it should be =COUNTIFS(A:A,"Y9",B:B,">50").
Q2
C — SUMIFS puts the sum_range first, not last. The points column must come first: =SUMIFS(C:C,A:A,"Y9",B:B,F1).
Q3
C — A criterion that includes an operator must be in quotes. It should be ">90", not >90.
Q4
B — COUNTIF only handles one criterion. To test two criteria at once, use COUNTIFS.
Q5
D — The last criteria range, C:C, has no criterion after it. Every range needs a criterion, so add F1: =SUMIFS(D:D,B:B,"Tech",C:C,F1).