Grouping numbers into bins with COUNTIFS

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

Grouping numbers into bins with COUNTIFS

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula counts the values in B2:B30 that are at least 10 and less than 20?
A =COUNTIFS(B2:B30,">=10",B2:B30,"<20")
B =COUNTIFS(B2:B30,">10","<20")
2. You use bins 0-9, 10-19 and 20-29 for whole numbers. Are these bins set up correctly?
A Yes: equal width, no gaps and no overlaps
B No: the bins leave gaps between them
3. A student uses "<=20" as the upper limit for the 10-20 bin and ">=20" as the lower limit for the next bin. What happens?
A The value 20 is not counted in any bin (gap)
B The value 20 is counted in both bins (overlap)
4. Which pair of criteria gives the bin 20 up to (but not including) 30?
A ">=20" and "<30"
B ">20" and "<=30"
5. What does a COUNTIFS bin formula return?
A The total of all the values inside that bin
B The frequency: how many values fall inside that bin
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1=COUNTIFS(B2:B30,">=10",B2:B30,"<20") AThe upper limit uses <= so 20 is counted in this bin and again in the 20 to 29 bin. The bins overlap. Use "<20" instead.
2=COUNTIFS(B2:B30,">=20",B2:B30,"<30") BCounts how many values in B2:B30 are 10 or more and less than 20, giving the frequency of the 10 to 19 bin
3A bin includes its lower limit but excludes its upper limit, so a value of 20 is counted only in the 20 to 29 bin C=COUNTIFS(B2:B30,">=0",B2:B30,"<10")
4To get the frequency of each bin, use COUNTIFS with a lower limit and an upper limit on the same data range DCounts the values in the next equal-width bin, from 20 up to but not including 30
5=COUNTIFS(B2:B30,">=10",B2:B30,"<=20") EThe lower limit uses > so a value of exactly 10 is missed by this bin and by the 0 to 9 bin. This leaves a gap. Use ">=10".
6=COUNTIFS(B2:B30,">10",B2:B30,"<20") F=COUNTIFS(B2:B30,">=20",B2:B30,"<30")
C

Definitions

=COUNTIFS(B2:B30,">=10",B2:B30,"<20")
D

Fill in the Blanks

Complete each line of code by filling in the missing parts.

1.
=________(B2:B30,">=10",B2:B30,"<20")
2.
=COUNTIFS(B2:B30,"__10",B2:B30,"<20")
3.
=COUNTIFS(B2:B30,">=10",B2:B30,"_20")
4.
=COUNTIFS(B2:B30,">=20",______,"<30")
5.
=COUNTIFS(B2:B30,">=10",B2:B30,"<20") =COUNTIFS(B2:B30,">=__",B2:B30,"<30")
E

Spot the Error

Shade in the letter of the line that contains an error.

Question 1
A Bin 1: 0 to under 5
B =COUNTIFS(B:B,">=0",B:B,"<=5")
C Bin 2: 5 to under 10
Question 2
A =COUNTIFS(B:B,">=0",B:B,"<5")
B Bin 2: 5 to under 10
C =COUNTIFS(B:B,">5",B:B,"<10")
Question 3
A Bin 2: 5 to under 10
B =COUNTIF(B:B,">=5",B:B,"<10")
C Bin 3: 10 to under 15
Question 4
A Bin 1: 0 to under 5
B =COUNTIFS(B:B,>=0,B:B,"<5")
C Bin 2: 5 to under 10
Question 5
A Scores are in column B
B Bin 2: 5 to under 10
C =COUNTIFS(B:B,">=5",C:C,"<10")

Answer Key

Grouping numbers into bins with COUNTIFS
Section A: Quick Questions
Q1
A — =COUNTIFS(B2:B30,">=10",B2:B30,"<20")
Q2
A — Yes: equal width, no gaps and no overlaps
Q3
B — The value 20 is counted in both bins (overlap)
Q4
A — ">=20" and "<30"
Q5
B — The frequency: how many values fall inside that bin
Section B: Match the Pairs
Q1
=COUNTIFS(B2:B30,">=10",B2:B30,"<20") → Counts how many values in B2:B30 are 10 or more and less than 20, giving the frequency of the 10 to 19 bin
Q2
=COUNTIFS(B2:B30,">=20",B2:B30,"<30") → Counts the values in the next equal-width bin, from 20 up to but not including 30
Q3
A bin includes its lower limit but excludes its upper limit, so a value of 20 is counted only in the 20 to 29 bin → =COUNTIFS(B2:B30,">=20",B2:B30,"<30")
Q4
To get the frequency of each bin, use COUNTIFS with a lower limit and an upper limit on the same data range → =COUNTIFS(B2:B30,">=0",B2:B30,"<10")
Q5
=COUNTIFS(B2:B30,">=10",B2:B30,"<=20") → The upper limit uses <= so 20 is counted in this bin and again in the 20 to 29 bin. The bins overlap. Use "<20" instead.
Q6
=COUNTIFS(B2:B30,">10",B2:B30,"<20") → The lower limit uses > so a value of exactly 10 is missed by this bin and by the 0 to 9 bin. This leaves a gap. Use ">=10".
Section C: Definitions
Q1
=COUNTIFS(B2:B30,">=10",B2:B30,"<20") — Counts the numbers in a range that are at least the lower limit and less than the upper limit, giving the frequency of one bin. Equal-width bins must not leave gaps or overlap.
Section D: Fill in the Blanks
Q1
=COUNTIFS(B2:B30,">=10",B2:B30,"<20")
Q2
=COUNTIFS(B2:B30,">=10",B2:B30,"<20")
Q3
=COUNTIFS(B2:B30,">=10",B2:B30,"<20")
Q4
=COUNTIFS(B2:B30,">=20",B2:B30,"<30")
Q5
=COUNTIFS(B2:B30,">=10",B2:B30,"<20") =COUNTIFS(B2:B30,">=20",B2:B30,"<30")
Section E: Spot the Error
Q1
B — The upper limit uses <=5, so a score of 5 is counted in this bin and again in the next bin (>=5). Bins must not overlap. It should be "<5".
Q2
C — The lower limit ">5" leaves out a score of exactly 5. The previous bin stops below 5, so 5 falls into a gap. It should be ">=5".
Q3
B — COUNTIF only accepts one range and one criterion. To apply a lower and an upper limit together you need COUNTIFS.
Q4
B — The criterion >=0 is missing its quotation marks. Criteria with an operator must be written as text, so it should be ">=0".
Q5
C — The second criterion tests column C, but the scores are in column B. Both limits must be applied to the same range, so it should be B:B.