Why COUNTIF doesn't find the row you know is there
A column of account codes or part numbers stored as text with leading zeros — "003607" — and a COUNTIF that should obviously match it comes back short. The actual rule is subtler than "text matches text, numbers match numbers."
We shipped this bug ourselves. Basis's COUNTIF/SUMIF used to coerce a text criteria straight to a number, which meant it could never match a text cell — COUNTIF(range,"003607") silently returned 0 whenever the only matching cell held "003607" as text. Fixed in PR #188; the numbers below are post-fix and independently checked against LibreOffice.
mismatches against Excel's own cached values, across 4,033,846 real formulas in 10,703 FUSE workbooks, moved by this one fix — zero-mismatch rate rising from 97.13% to 97.31%. One precise semantic correction, not a heuristic, measurably changed how often a 4-million formula corpus agrees with Excel.
The answer
- A criteria written as a bare or quoted number —
COUNTIF(range,3607)— matches numeric cells equal to3607only. It has no text half. - A criteria written as text whose characters parse as a number —
COUNTIF(range,"3607")or"=3607"— matches numeric cells by value and text cells by their literal, case-insensitive characters. Leading zeros are part of that text identity:"003607"finds the text"003607"and the number3607(equal by value); it does not find the text"3607"(different characters). - Order comparisons (
>,<,>=,<=) stay strictly numeric — a text cell is invisible to">3000"no matter what it says.
Reproduce it
The exact fixture from our own regression test — column A: text "003607", "3607", "abc", "0012", then the numbers 3607 and 12; column C: 10, 20, 30, 40, 50, 60. Every result below is LibreOffice's own answer on this shape:
| Formula | Result |
|---|---|
COUNTIF(A1:A6,"003607") | 2 |
COUNTIF(A1:A6,"3607") | 2 |
SUMIF(A1:A6,"003607",C1:C6) | 60 |
SUMIFS(C1:C6,A1:A6,"3607") | 70 |
COUNTIF(A1:A6,"0012") | 2 |
COUNTIF(A1:A6,"12") | 1 |
COUNTIF(A1:A6,"ABC") | 1 |
COUNTIF(A1:A6,3607) (bare number) | 1 |
SUMIF(A1:A6,3607,C1:C6) | 50 |
COUNTIF(A1:A6,">3000") | 1 |
COUNTIF(A1:A6,"<>003607") | 4 |
COUNTIF(A1:A6,3607) (a bare number) finds only the numeric cell — result 1. COUNTIF(A1:A6,"3607") (the same value as text) finds that same numeric cell and the text cell "3607" — result 2. That asymmetry, not a simple type-matching rule, is the actual behavior.
A smaller version you can build in under a minute:
A1: 5 (number) A2: "5" (text) A3: 10 (number) A4: "abc" (text) COUNTIF(A1:A4, 5) -> 1 (numeric only) COUNTIF(A1:A4, "5") -> 2 (numeric 5 AND text "5") SUMIF(A1:A4, 5) -> 5 SUMIF(A1:A4, "5") -> 5 (text "5" matched, contributed 0 to the sum) COUNTIF(A1:A4, ">=5") -> 2 (order comparison: numeric only, "abc" excluded)
How common is this in the wild
SUMIF/COUNTIF (or their -S plural forms) appear in 6.9% of formula-bearing FUSE workbooks (252 of 3,640) and 4.7% of Enron's (430 of 9,220) — common enough that this exact miscount was silently wrong in a meaningful slice of real files before the fix, and not a corner anyone would think to double-check by hand.
Relying on COUNTIF/SUMIF over a text-and-number column? Re-verify the count against Excel's own cached value instead of trusting either engine's arithmetic blind.