Excel like a finance pro.
Statistical function
FREQUENCY
Counts how many values fall into each bin, returned as an array.
When to use it
Counts how many values fall into each bin. Bins are upper bounds; the result has one more element than the bins for everything above the last bound. Returns a vertical array.
The shape of it
- Syntax
=FREQUENCY(data_array, bins_array)
Worked examples
Three bins
=FREQUENCY({1,5,12,20,25},{10,20}) → {2;2;1}
Two values up to 10, two from 11 to 20, one above 20.
Histogram counts
=FREQUENCY(B2:B200,{100,250,500,1000}) → {34;80;61;20;4}
Order sizes into five buckets. The last bucket is over 1,000.
Count distinct numbers
=SUM(FREQUENCY(A2:A100,A2:A100)>0) → 57
Distinct numeric values: each unique value gets its own non-empty bin.
Worth knowing
- Select the output cells (one more than the bins) and confirm with Ctrl+Shift+Enter in older Excel; Microsoft 365 spills.
- Bins do not need to be evenly spaced.
- COUNTIFS with two conditions per bin does the same in a copy-down friendly way.
Where it goes wrong
- Blanks and text are ignored.
- The extra top bin is easy to forget, leaving the last count off the sheet.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.