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)

Want to practice FREQUENCY in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

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

=FREQUENCY(data_array, bins_array) Explain

Arguments

ArgumentRequired?What it does
data_arrayYesThe values to count. Text and blanks are ignored.
bins_arrayYesThe 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.

E2fx=FREQUENCY(C2:C8,D2:D4)
ABCDE
1RepRegionSalesUp toCount
2AnaNorth62000450001
3BenSouth48500550003
4CyNorth50000650002
5DiEast71250(over)1
6EdSouth39800
7FayNorth55400
8GusEast48500
=FREQUENCY(C2:C8,D2:D4) Explain

The four counts add up to the 7 reps.

Example 2 — Count distinct numbers

A classic trick: FREQUENCY of the data against itself.

=SUM(--(FREQUENCY(C2:C8,C2:C8)>0)) Explain

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

Kyle Stott

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.