Why does your COUNTIFS formula return 0 when you know the values exist? Why does the pivot table bin your ages into ranges like '25-25' instead of '25-34'? Why does sorting the source data *before* building the table break everything?
The answer isn’t missing functions or broken data—it’s that Excel doesn’t auto-detect intervals unless you tell it *exactly* where the breaks live. And most people build the bins *after* writing formulas—guaranteeing mismatched ranges and ghost zeros. Let’s fix that.
The Problem
You’ve just imported 217 customer satisfaction scores from a Net Promoter Score (NPS) survey. They’re in column A, rows 2–218: integers from –10 to +10. You need to report how many respondents gave scores in each 5-point band: –10 to –6, –5 to –1, 0 to 4, 5 to 10.
You try =COUNTIF(A2:A218,"<=-6"), then subtract the next one… but your totals don’t add up to 217. You check manually—there are 12 scores of –6. Your formula counts them in *both* the –10-to–6 bucket *and* the –5-to–1 bucket. You’re double-counting—and you don’t even realize it yet.
| Symptom | Cause | Fix |
|---|---|---|
| COUNTIFS returns 0 for expected ranges | Bin boundaries overlap or use inclusive/exclusive logic inconsistently (e.g., <= vs <) | Define non-overlapping bins *first*, then use COUNTIFS with strict half-open intervals: >=X and <Y |
| PivotTable groups numbers oddly (e.g., "10-10" instead of "5-10") | Excel auto-bins using arbitrary starting points—ignoring your intended class width | Right-click pivot field → "Group" → set "Starting at", "Ending at", and "By" manually (e.g., Starting at –10, By 5) |
| Totals don’t match row count | Values falling *exactly* on bin edges get assigned to wrong group or omitted | Use ROUNDUP((value - min)/bin_width,0) to assign unambiguously—or better yet, define bins as text labels ("–10 to –6") and map with XLOOKUP |
The Solution
We’ll build a clean frequency table in under 90 seconds—with no pivot tables, no array formulas, and no guesswork. You’ll use only COUNTIFS, a hand-built bin table, and one critical keyboard shortcut.
- Define your bins *before* counting. In column D, starting at D2, list your lower bounds:
–10,–5,0,5. In E2:E5, list upper bounds:–6,–1,4,10. These are your *inclusive* ranges—but we’ll treat them as half-open in formulas. Yes, this feels backwards. (Trust me—I learned this the hard way after three misreported client dashboards.) - Create a descriptive label column. In F2, enter:
=D2&" to "&E2. Drag down to F5. You’ll get "–10 to –6", "–5 to –1", etc. This avoids confusion when ranges aren’t uniform (e.g., if your last bin is "5 to 10" but others are width 5). - Write the first COUNTIFS—and copy it right. In G2, enter:
=COUNTIFS($A$2:$A$218,">="&D2,$A$2:$A$218,"<="&E2)
This counts values ≥ –10 AND ≤ –6. That’s correct *only because* your bins are designed to be inclusive and non-overlapping. But here’s the surprise: if any score equals –5, it belongs in bin 2—not bin 1. So your bin edges *must* abut: –6 and –5 are consecutive integers. No gaps. No overlaps. - Copy down—but verify totals. Select G2, press Ctrl+C, select G2:G5, press Ctrl+V. Then sum G2:G5. It must equal 217. If not, check for blank cells or text in A2:A218. Use
=ISNUMBER(A2)in H2 and drag down—you’ll spot non-numeric entries instantly.
Here’s what your final table looks like:
| Bin Label | Frequency |
|---|---|
| –10 to –6 | 19 |
| –5 to –1 | 47 |
| 0 to 4 | 83 |
| 5 to 10 | 68 |
| Total | 217 |
Notice: no pivot table. No Data Analysis ToolPak. Just four clean formulas—and you control every boundary.
Going Further
Once you’ve got the basics down, here’s where most people stop—but where real utility begins.
Add relative frequency. In H2, enter =G2/SUM($G$2:$G$5) and format as % (Alt+H+P+2). Drag down. Now stakeholders see “38.7% scored 0–4” — not just raw counts.
Handle open-ended bins. For “50+ years old”, set D6 = 50, leave E6 blank, and adjust the formula in G6 to:=COUNTIFS($B$2:$B$184,">="&D6)
(Assuming ages are in column B, rows 2–184.)
Dynamic bins with named ranges. Select D2:E5 → Formulas tab → “Define Name” → name it Bins. Then in G2, use:=COUNTIFS($A$2:$A$218,">="&INDEX(Bins,ROW()-1,1),$A$2:$A$218,"<="&INDEX(Bins,ROW()-1,2))
Now if you insert a row in D2:E5, the formula updates automatically.
Chart it—without copying. Select F2:G5 → Alt+N+C → choose “Clustered Column”. Right-click chart → “Select Data” → click “Switch Row/Column” to make bins horizontal. Done.
When NOT to Use This
This method shines for small-to-medium datasets (under 50k rows) with discrete or well-behaved numeric data. But avoid it when:
- You’re working with text categories that already exist (e.g., “Product A”, “Product B”). Use
=COUNTIF(range,"Product A")—no bins needed. - Your data has >100 unique values and you want histogram-like grouping *on the fly*. PivotTables with manual grouping (Alt+D+P → drag field → right-click → Group) handle large numeric sets faster—and auto-adjust if new data arrives.
- You need cumulative frequencies. Don’t chain COUNTIFS. Instead, in I2:
=SUM($G$2:G2), then drag down. But if your source range changes often, switch to=SUMIFSwith expanding references. - You’re sharing with users who edit formulas. Encapsulate the logic in a LAMBDA (if you have Microsoft 365):
=LAMBDA(data,bins,COUNTIFS(data,">="&INDEX(bins,SEQUENCE(ROWS(bins)),1),data,"<="&INDEX(bins,SEQUENCE(ROWS(bins)),2)))
Name itFREQTABLE, then call=FREQTABLE(A2:A218,D2:E5). Cleaner—but requires subscription.
Also: never use this for time-based data like timestamps unless you convert to decimal hours first. Excel treats times as fractions of a day—so “8:00 AM” = 0.333. Your bin logic will fail silently.
Keyboard Shortcuts
These save 10–15 seconds per frequency table—and compound fast across weekly reports.
| Action | Shortcut | Notes |
|---|---|---|
| Open PivotTable dialog | Alt+D+P | Still useful for quick exploratory grouping |
| Format as percentage | Alt+H+P+2 | Applies 0% format—press again for 1 decimal |
| Insert function dialog | Shift+F3 | Type “COUNTIFS” to jump straight to syntax help |
| Fill down formula | Ctrl+D | Faster than copy-paste when formulas are adjacent |
| Toggle formula view | Ctrl+` | See all formulas at once—spot hardcoded values instantly |
