The first thing most people do when they need a frequency count is write a long chain of COUNTIF formulas — one for each bin, copying down from A2 to A20. That’s fragile, breaks when bins change, and won’t auto-update if new data arrives. Worse: it gives you counts, but not a true frequency distribution. You’re not just counting — you’re grouping values into ranges. And COUNTIF doesn’t know what a bin is.
Quick Answer
Use FREQUENCY(data_array, bins_array) as an array formula (Ctrl+Shift+Enter on older Excel; Enter in Microsoft 365). It returns counts for each bin *and* a final count for values above the highest bin — all in one go. No dragging. No nested IFs. Just one formula in F2:F8 that spills correctly.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| FREQUENCY() | Select output range (e.g., F2:F8), type =FREQUENCY(A2:A50,B2:B7), press Ctrl+Shift+Enter (or Enter in 365) | Numeric grouped distributions (e.g., sales by $10k brackets) | Only works with numbers; bins must be ascending; outputs one more cell than bins entered |
| PivotTable + Grouping | Insert PivotTable → drag field to Rows → right-click row label → Group → set Starting/Ending/By values | Mixed data types, quick visual exploration, dynamic updates | Can’t easily export raw bin counts to formulas; grouping resets if source data changes structure |
| SUMPRODUCT + logicals | =SUMPRODUCT((A2:A50>=B2)*(A2:A50| One-off checks, non-contiguous bins, compatibility with Excel 2007+ |
Manual setup per bin; error-prone with open-ended top bin (e.g., “$100k+”) |
|
| Dynamic Array + SEQUENCE (365) | =FREQUENCY(A2:A50, B2:B7) — no Ctrl+Shift+Enter needed; spills automatically | Modern Excel users who want live, responsive bins without legacy array syntax | Not available in Excel 2019 or earlier; requires Microsoft 365 subscription |
Method 1 Deep Dive
Let’s say your sales team logged 47 deals in column A (A2:A48):
42100, 68900, 31200, 94500, 52700, …
You want to know how many fell into these ranges: $30k–$49,999, $50k–$69,999, $70k–$89,999, $90k+.
First, list bin upper limits in B2:B5: 49999, 69999, 89999, 999999. Yes — that last one isn’t elegant, but FREQUENCY needs an upper bound. (Pro tip: Use =MAX(A2:A48)+1 in B5 instead of guessing.)
Select cells F2:F6 — five cells, even though you only have four bins. That’s intentional. FREQUENCY always returns n+1 results: counts for each bin, plus a final count for values > last bin.
Type =FREQUENCY(A2:A48,B2:B5) and press Ctrl+Shift+Enter (Alt+M, M, E in older Excel ribbon navigation). You’ll see:
F2: 12 (≤49,999)
F3: 15 (≤69,999 but >49,999)
F4: 8 (≤89,999 but >69,999)
F5: 9 (≤999,999 but >89,999)
F6: 3 (values >999,999 — but in our case, zero)
Notice how F6 catches outliers — useful when auditing data quality. If you get a non-zero value there, you’ve got typos like “1200000” instead of “120000”.
Method 2 Deep Dive
Now imagine your data includes names and regions too — say columns A:C contain:
A2:A50 = Sales Amount
B2:B50 = Rep Name (e.g., "Sarah Chen", "Diego Mendoza")
C2:C50 = Region ("APAC", "EMEA", "Americas")
You want frequency *by region and bracket*. PivotTable is faster here — and less error-prone.
Select A1:C50 → Insert → PivotTable → OK. Drag “Region” to Rows, “Sales Amount” to Rows again, then right-click any sales number → Group. Set Starting at 30000, Ending at 120000, By 20000. Click OK.
Your PivotTable now shows:
APAC → $30,000–$49,999: 5
APAC → $50,000–$69,999: 7
EMEA → $30,000–$49,999: 3
… and so on.
Here’s the counterintuitive part: if you later add new rows below C50, the PivotTable won’t auto-include them unless you refresh (Alt+F5) *and* your source was an Excel Table (Ctrl+T). So before building the Pivot, convert A1:C50 to a Table — then refresh pulls in new rows automatically.
Need those counts elsewhere? Right-click the PivotTable → Copy, then Paste Special → Values. Or use GETPIVOTDATA() — but only if your layout stays fixed.
Cheat Sheet
| Task | Formula / Shortcut | Notes |
|---|---|---|
| Enter FREQUENCY as array | Select output range → type formula → Ctrl+Shift+Enter | Legacy Excel only. In 365, just press Enter. |
| Create bins dynamically | In B2: =MIN($A$2:$A$48), B3: =B2+20000, drag down | Adjust step value to match your data spread. |
| Refresh PivotTable | Alt+F5 | Only works if source is a Table or you manually extend the range. |
| Get bin labels as text | In G2: =IF(F2="","",B1&"–"&B2) → adjust for upper bounds | FREQUENCY gives counts only — you build labels separately. |
| Count values in a single bin | =COUNTIFS(A2:A48,">="&B2,A2:A48,"<"&B3) | Safer than COUNTIF for open-ended ranges. Works with text too. |
