Excel FREQUENCY Function

last modified October 5, 2026

The FREQUENCY function is a statistical function in Excel that calculates how often values occur within specified ranges, called bins. It returns a vertical array of counts that forms a frequency distribution. This tutorial covers the syntax, how bins and the extra overflow element work, the array-entry rules, worked examples with scores, ages, salaries, and prices, a comparison with COUNTIFS, and the problems that appear when the arguments are set up incorrectly.

FREQUENCY Function Basics

FREQUENCY takes a set of numeric values and a set of bin upper limits, then counts how many values fall into each interval. It always returns one more count than there are bins, so the last value captures everything above the highest bin. In Excel 2019 and earlier the formula must be confirmed as an array formula; in Microsoft 365 and Excel 2021 it spills automatically.

Component	Description
Function Name	FREQUENCY
Syntax	=FREQUENCY(data_array, bins_array)
Return Value	A vertical array with one more element than bins_array
Array Entry	Ctrl+Shift+Enter in Excel 2019 and earlier; spills automatically in Microsoft 365 and Excel 2021

This table breaks down the essential components of the FREQUENCY function. It shows the function name, syntax format, return value shape, and the entry rules for legacy and modern Excel versions.

Argument	Meaning
data_array	The values to analyze. It can be a range, an array constant, or an expression that returns numbers. Blank cells and text are ignored. If it contains no numeric values, every count is zero.
bins_array	The upper limits of the intervals, in ascending order. Each bin value is included in its own interval. If it is empty, FREQUENCY returns a single count of all the data values.

The two arguments are both required. data_array supplies the raw numbers and bins_array defines the boundaries. Neither argument has to be a single column, but the result is always a single vertical column of counts.

How Bins and the Extra Element Work

A bin is the upper limit of an interval. FREQUENCY walks through the bins from smallest to largest and counts values that are less than or equal to the current bin but greater than the previous bin. The first bin therefore counts everything up to and including its value, and each later bin excludes the values already counted.

Result position	Values counted
1st element	Values less than or equal to the first bin
2nd element	Values greater than the first bin and less than or equal to the second bin
...	...
nth element	Values greater than the previous bin and less than or equal to the nth bin
Extra element (n+1)	Values greater than the highest bin

Because of that final overflow element, a formula with four bins needs five result cells. In Microsoft 365 and Excel 2021 the extra counts spill down automatically. In Excel 2019 and earlier you must select the whole output range, which is one cell taller than the bin list, before pressing Ctrl+Shift+Enter. Bins should be sorted in ascending order and should not be duplicated, otherwise the intervals overlap and the distribution becomes misleading.

Basic FREQUENCY Example

This example demonstrates the simplest use of FREQUENCY with test scores and grade boundaries.

A (Scores)	B (Grade Boundaries)	C (Frequency)
78	60	0
85	70	1
92	80	2
65	90	1
72		1

Select cells C1:C5, enter the formula, and press Ctrl+Shift+Enter in Excel 2019 and earlier. In Microsoft 365 and Excel 2021, enter it in C1 and press Enter.

=FREQUENCY(A1:A5, B1:B4)

There are four bins, so the result occupies five cells. The counts are 0, 1, 2, 1, and 1: no score is ≤60, one score (65) is 61-70, two scores (78 and 72) are 71-80, one score (85) is 81-90, and one score (92) is greater than 90.

FREQUENCY with Equal Width Bins

This example shows how to create equal-width bins for analyzing age distribution data.

A (Ages)	B (Bin Upper Limits)	C (Frequency)
23	20	1
35	30	2
42	40	1
28	50	1
19		0

=FREQUENCY(A1:A5, B1:B4)

This formula creates age groups ≤20, 21-30, 31-40, 41-50, and >50. The counts are 1, 2, 1, 1, and 0: one age is ≤20 (19), two are 21-30 (23 and 28), one is 31-40 (35), one is 41-50 (42), and none is above 50. Equal-width bins are useful for consistent data analysis.

FREQUENCY with Unequal Bins

This example demonstrates using custom, unequal bin sizes for salary data analysis.

A (Salaries)	B (Salary Brackets)	C (Frequency)
45000	30000	0
62000	50000	2
38000	70000	2
55000	100000	1
82000		0

=FREQUENCY(A1:A5, B1:B4)

This formula creates salary brackets ≤30,000, 30,001-50,000, 50,001-70,000, 70,001-100,000, and >100,000. The counts are 0, 2, 2, 1, and 0: no salary is ≤30,000, two are 30,001-50,000 (45,000 and 38,000), two are 50,001-70,000 (62,000 and 55,000), one is 70,001-100,000 (82,000), and none is above 100,000. Unequal bins allow for meaningful categorization based on business needs.

FREQUENCY with Dynamic Arrays

In Microsoft 365 and Excel 2021, FREQUENCY automatically spills results with dynamic arrays. This example analyzes product prices.

A (Prices)	B (Price Ranges)	C (Frequency)
15.99	10	1
8.50	20	2
25.00	30	1
12.75		0

=FREQUENCY(A1:A4, B1:B3)

This formula counts products in the ranges ≤$10, $10.01-$20, $20.01-$30, and >$30. The counts are 1, 2, 1, and 0: one price is ≤$10 (8.50), two are $10.01-$20 (15.99 and 12.75), one is $20.01-$30 (25.00), and none is above $30. Because the formula spills, it occupies C1:C4 without any manual range selection.

FREQUENCY with Empty Cells

This example shows how FREQUENCY handles empty cells in the data array.

A (Values)	B (Bins)	C (Frequency)
5	5	1
	10	1
12		1
8

=FREQUENCY(A1:A4, B1:B2)

Empty cells in the data array are ignored. The counts are 1, 1, and 1: one value is ≤5 (5), one is 6-10 (8), and one is greater than 10 (12). The empty cell A2 does not affect the counts and is not treated as a zero.

FREQUENCY with Text Values

This example demonstrates FREQUENCY's behavior when the data array contains text values.

A (Mixed Data)	B (Bins)	C (Frequency)
15	10	0
"Text"	20	1
25		1

=FREQUENCY(A1:A3, B1:B2)

Text values in the data array are ignored, just like blanks. The counts are 0, 1, and 1: no numeric value is ≤10, one is 11-20 (15), and one is greater than 20 (25). The text in A2 is skipped, so it is not counted in any bin.

FREQUENCY Compared with COUNTIFS

A frequency distribution can also be built with COUNTIFS. The difference is that COUNTIFS returns one count per formula, while FREQUENCY returns the whole distribution from a single formula. Using the ages in A1:A5 from the equal-width example, the two approaches produce the same counts.

Bin	FREQUENCY result	COUNTIFS equivalent
≤20	1	=COUNTIFS(A1:A5, "<=20")
21-30	2	=COUNTIFS(A1:A5, ">20", A1:A5, "<=30")
31-40	1	=COUNTIFS(A1:A5, ">30", A1:A5, "<=40")
41-50	1	=COUNTIFS(A1:A5, ">40", A1:A5, "<=50")
>50	0	=COUNTIFS(A1:A5, ">50")

The counts match the age example above. Use COUNTIFS when you want a labeled bin table or need to combine frequency counting with other criteria, and use FREQUENCY when you want a compact array of counts that can feed a chart or further calculation.

Entering FREQUENCY as an Array Formula

The entry method depends on the Excel version. Getting this step wrong is the most common source of confusion, because a formula entered normally into a single cell shows only the first count.

Version	How to enter
Microsoft 365, Excel 2021 and later	Enter the formula in the top-left cell and press Enter. The remaining counts spill down automatically.
Excel 2019 and earlier	Select a range one cell taller than the bin list, type the formula, and press Ctrl+Shift+Enter. Excel adds curly braces and fills every selected cell.

{=FREQUENCY(A1:A5, B1:B4)}

The braces shown above are inserted by Excel, not typed by hand. If you type them yourself the formula is stored as text and will not calculate. To return horizontal results instead, wrap the function in TRANSPOSE, as in =TRANSPOSE(FREQUENCY(A1:A5, B1:B4)).

FREQUENCY Errors and Troubleshooting

Cause	Symptom	Fix
The formula is entered in a single cell in Excel 2019 or earlier	Only the first count appears	Select the full output range and confirm with Ctrl+Shift+Enter
The output range is the same height as the bins	The overflow count is missing	Select one extra cell for the values above the highest bin
The bins are unsorted or duplicated	Counts that do not add up to the data total, or odd intervals	Sort the bins in ascending order and remove duplicates
Text or blank cells in data_array	Those cells are not counted	This is normal behavior; clean the data if the values should be numeric
bins_array is empty	A single count of all data values	Supply bin upper limits to build a distribution
A spilled range is blocked by existing data	#SPILL! error	Clear the cells below the formula or move the formula
The formula is edited inside a legacy array range	Excel refuses the change	Select the whole array range first, then edit and confirm again
data_array contains error values	The same error is passed through	Remove or mask the error values before counting

The FREQUENCY function is a powerful tool for statistical analysis in Excel. It turns a column of raw numbers into a compact frequency distribution with a single formula. Remember that it always returns one more count than there are bins, that the final count captures values above the highest bin, that bins should be sorted in ascending order, and that blank and text cells are ignored. In Microsoft 365 and Excel 2021 the result spills automatically, while earlier versions require an array formula entered over a range one cell taller than the bin list. With those rules in mind you can analyze test scores, salaries, ages, prices, and any other numeric dataset reliably.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.