Statistics function

Excel FREQUENCY function

Q: How do I make a frequency table in Excel?

List the upper limit of each band in a column, then use =FREQUENCY(data,bins) next to it. In Excel 365 the counts spill down automatically, with one extra row for values above the top band.

Q: Can I use COUNTIFS instead of FREQUENCY?

Yes. =COUNTIFS(B2:B9,">"&D2,B2:B9,"<="&D3) counts one band and is easier to label, but you need one formula per band. FREQUENCY does all the bands at once.

FREQUENCY counts how many numbers fall in each bin of a distribution: up to the first bin value, then above that up to the next, and so on, plus one extra count for everything above the last bin. It returns the counts as a column, which in Excel 365 spills on its own. Use it for score bands, age groups and histogram tables.

=FREQUENCY(data_array, bins_array)

Arguments

data_array: The numbers to count. Blank cells and text are ignored.
bins_array: The upper limit of each bin, such as 59, 69, 79, 89. Each bin counts values above the previous limit up to and including its own.

FREQUENCY examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	D
1	Student	Score	Up to
2	Maya	92	59
3	Leo	67	69
4	Priya	85	79
5	Sam	58	89
6	Nora	79
7	Eli	96
8	Ava	74
9	Ben	85

Download this sheet as an Excel practice file (.xlsx)

=FREQUENCY(B2:B9,D2:D5)
Result: spills 5 rows × 1 column
1
1
2
2
2
Four bins give five counts: 1 score up to 59, 1 from 60 to 69, 2 from 70 to 79, 2 from 80 to 89, and 2 above 89 in the extra fifth row.
=FREQUENCY(B2:B9,{69,79,89})
Result: spills 4 rows × 1 column
2
2
2
2
The bins can be typed in braces. Three bins give four counts: 2, 2, 2 and 2. The result is always a column, even with bins typed across.
=INDEX(FREQUENCY(B2:B9,D2:D5),5)
Result: 2
Picks one count out of the list: the fifth, scores above 89, is 2.
=SUM(--(FREQUENCY(B2:B9,B2:B9)>0))
Result: 7
Counts the different scores: 7. Using the data as its own bins puts each repeat of a value in its first bin and 0 in the others, so counting the bins above 0 counts unique numbers.

How to use FREQUENCY

Type the upper limit of each bin in a column, smallest first.
Click the cell where the counts should start, with one more empty row below than you have bins.
Type =FREQUENCY( then select the data, a comma, and the bins.
Press Enter. In Excel 2019 and older, select the whole output range first and press Ctrl+Shift+Enter.

Common FREQUENCY mistakes

Forgetting the extra count

FREQUENCY returns one more value than there are bins: the last one counts everything above the highest bin. Leave room for it or the spill shows #SPILL!.

Reading bins as lower limits

A bin value is the top of its band, included. With bins 59 and 69, a score of 69 is counted in the second bin, not the third.

Counting text numbers

Scores stored as text are ignored. Convert them with VALUE or Data > Text to Columns before counting.

FREQUENCY FAQ

How do I make a frequency table in Excel?

List the upper limit of each band in a column, then use =FREQUENCY(data,bins) next to it. In Excel 365 the counts spill down automatically, with one extra row for values above the top band.

Why does FREQUENCY return one more value than bins?

The extra value counts everything greater than the highest bin, so no number is left out. If you are sure nothing is above the last bin, it is simply 0.

Can I use COUNTIFS instead of FREQUENCY?

Yes. =COUNTIFS(B2:B9,">"&D2,B2:B9,"<="&D3) counts one band and is easier to label, but you need one formula per band. FREQUENCY does all the bands at once.

Guides that use FREQUENCY

Related functions

Updated November 4, 2026