← VCE Spreadsheets, SQL & Visualisation

Grouping numbers into bins with COUNTIFS

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.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 21, VCE Data Analytics - U3O1 - Data Analytics — Module 85

Understanding Grouping numbers into bins with COUNTIFS in VCE Spreadsheets, SQL & Visualisation

What it does: The >= includes 20 in this bin, and the < in the previous bin (<20) leaves it out. That stops overlaps and gaps. One COUNTIFS formula per bin gives that bin's frequency. This one counts values from 0 up to but not including 10. COUNTIFS needs the range repeated for each condition: range, criterion, range, criterion. Each bin is 10 wide, and for whole numbers 9 is followed straight away by 10, so there is no gap and no overlap. Using <= in one bin and >= in the next counts 20 twice. Use "<20" for the upper limit and ">=20" for the next lower limit.

Common mistakes: 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". 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".

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Grouping numbers into bins with COUNTIFS to a class →

This module's key terms are also in a printable crossword (Modules 21–34) and word search (Modules 4–34).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which formula counts the values in B2:B30 that are at least 10 and less than 20?
=COUNTIFS(B2:B30,">=10",B2:B30,"<20")=COUNTIFS(B2:B30,">10","<20")
Matches
=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.
Applying
Meanings
Term
=COUNTIFS(B2:B30,">=10",B2:B30,"<20")
Meaning
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.
Practice
=COUNTIFS(B2:B30,">=10",B2:B30,"<20")
Testing
Gaps
=(B2:B30,">=10",B2:B30,"<20")
Check Answer
Spots
1Bin 1: 0 to under 5
2=COUNTIFS(B:B,">=0",B:B,"<=5")

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Cosmic Owl' badge — Comprehension Guru, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

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")
COUNTIFS needs the range repeated for each condition: range, criterion, range, criterion.
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
Each bin is 10 wide, and for whole numbers 9 is followed straight away by 10, so there is no gap and no overlap.
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) ✓
Using <= in one bin and >= in the next counts 20 twice. Use "<20" for the upper limit and ">=20" for the next lower limit.
4. Which pair of criteria gives the bin 20 up to (but not including) 30?
A. ">=20" and "<30" ✓
B. ">20" and "<=30"
Using >=20 and <30 means 20 belongs to this bin and 30 belongs to the next bin, so nothing is missed or doubled.
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 ✓
COUNTIFS counts how many values meet the conditions. SUMIFS would add them up.

Match the Pairs

=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
=COUNTIFS(B2:B30,">=20",B2:B30,"<30") → Counts the values in the next equal-width bin, from 20 up to but not including 30
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")
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")
=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.
=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".

Key Term

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

Fill in the Blanks

1. =COUNTIFS(B2:B30,">=10",B2:B30,"<20")
Hint: Type the function name that counts cells meeting several conditions at once.
2. =COUNTIFS(B2:B30,">=10",B2:B30,"<20")
Hint: Type the comparison operator that includes the lower limit (at least this value).
3. =COUNTIFS(B2:B30,">=10",B2:B30,"<20")
Hint: Type the operator that keeps the upper limit out of this bin, so bins do not overlap.
4. =COUNTIFS(B2:B30,">=20",B2:B30,"<30")
Hint: Type the same data range that the upper limit condition must check.
5. =COUNTIFS(B2:B30,">=10",B2:B30,"<20") =COUNTIFS(B2:B30,">=20",B2:B30,"<30")
Hint: Type the lower limit that starts exactly where the previous bin ends, leaving no gap.

Spot the 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
Line 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".
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")
Line 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".
Question 3
A Bin 2: 5 to under 10
B =COUNTIF(B:B,">=5",B:B,"<10")
C Bin 3: 10 to under 15
Line B — COUNTIF only accepts one range and one criterion. To apply a lower and an upper limit together you need COUNTIFS.
Question 4
A Bin 1: 0 to under 5
B =COUNTIFS(B:B,>=0,B:B,"<5")
C Bin 2: 5 to under 10
Line B — The criterion >=0 is missing its quotation marks. Criteria with an operator must be written as text, so it should be ">=0".
Question 5
A Scores are in column B
B Bin 2: 5 to under 10
C =COUNTIFS(B:B,">=5",C:C,"<10")
Line 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.

Student with a class code? Join your class →