FREQUENCY Function
Counts how many values fall into each range (bin), as a list that spills down.
Category
Statistical
Available in
All versions, Microsoft 365
Returns
A vertical list of counts (one more than the number of bins)
Overview
FREQUENCY sorts values into bins. Each bin counts values above the previous bin and up to (and including) its own upper limit. An extra final count catches everything above the last bin.
It's the formula behind a histogram. In Microsoft 365 the counts spill automatically; in older versions select the output cells and press Ctrl+Shift+Enter.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| data_array | Yes | The values to count. Text and blanks are ignored. |
| bins_array | Yes | The upper limit of each bin, in ascending order. |
Examples
Example 1 — Sales bands
Bins at 45,000, 55,000 and 65,000 give four counts: up to 45k, 45k–55k, 55k–65k, and over 65k.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Up to | Count |
| 2 | Ana | North | 62000 | 45000 | 1 |
| 3 | Ben | South | 48500 | 55000 | 3 |
| 4 | Cy | North | 50000 | 65000 | 2 |
| 5 | Di | East | 71250 | (over) | 1 |
| 6 | Ed | South | 39800 | ||
| 7 | Fay | North | 55400 | ||
| 8 | Gus | East | 48500 |
The four counts add up to the 7 reps.
Example 2 — Count distinct numbers
A classic trick: FREQUENCY of the data against itself.
Returns 6 — 48,500 is only counted once.
Common mistakes
- One fewer count than expected. FREQUENCY always returns one more value than there are bins. Leave room for the extra "over" count.
- #SPILL! The cells below the formula aren't empty.
Notes
- Related functions: COUNTIFS, MODE.MULT, PERCENTILE.INC.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.