Post actualizado el día octubre 24, 2026 by DeiviSanzPlay
Why your text counts fail (and it’s not the formula)
Most incorrect counts in Excel don’t come from mistyping COUNTIF, but from not understanding what Excel considers «text». A cell with a space, a formula that returns «» or a number imported as text break the result without any warning appearing. Before touching the formula, check the source of the data.
Error 1: confusing an empty cell with a cell containing «»
If you use COUNTA over a range with formulas that return an empty string, you’ll count cells that visually appear blank. COUNTA counts any non-empty value, including the empty string from a formula. For real text, COUNTIF(range;»?*») is more reliable because it requires at least one character. Watch out: the asterisk doesn’t match empty strings, but it does match a space.
Error 2: invisible spaces that inflate the count
A case I see often: data pasted from an ERP or a CSV where every «empty» cell carries a space. COUNTA counts them as text and the total skyrockets. The practical solution is to clean with TRIM in a helper column before counting, or use an array formula with LEN(TRIM(range))>0. In versions with dynamic arrays, =SUM(–(LEN(TRIM(A2:A100))>0)) is enough.
Error 3: numbers stored as text
Excel distinguishes between the number 123 and the text «123». If you count with ISNUMBER or with numeric criteria, values imported as text are left out. According to Microsoft’s official documentation, the green error indicator (triangle) reveals these cases, but it doesn’t appear in very wide columns or in protected workbooks. Check with =ISNUMBER(A2) before accepting the total.
Error 4: criteria with poorly escaped wildcards
In COUNTIF, the characters *, ? and ~ have special meaning. If you’re literally looking for an asterisk (for example, in product references like «A*B»), you must escape it with a tilde: «~*». Without that detail, the criterion matches any string and the result has nothing to do with what’s expected.
Error 5: ranges of different sizes
When the criteria range and the count range don’t match in rows, Excel doesn’t always warn you. In COUNTIFS with several ranges, a one-row offset shifts all the criteria and produces plausible but false figures. Verify that all ranges have the same height; if you work with structured tables, use table references so they grow together.
Limitations worth accepting
No counting formula distinguishes uppercase from lowercase: COUNTIF treats «Madrid» and «madrid» the same. If you need that distinction, you’ll have to resort to SUMPRODUCT with EXACT, and even then performance drops on large sheets. There’s also no native function that counts only text without counting errors or logical values; for that you have to combine ISTEXT with an array sum, and ISTEXT doesn’t exist as such in all versions: it’s simulated with ESTEXTO or with a negated ISNUMBER.
My recommendation after debugging countless sheets: count first with COUNTA, compare with COUNTIF(range;»?*») and if the numbers don’t add up, check spaces and types before rewriting the formula. The error is almost never in the syntax.
