20 errors · Wrong results, no #ERROR

Function-Specific Errors

The trickiest kind — the formula returns a number, no red error appears, but the number is wrong. SUMIFS off by one row. COUNTIF counting blanks. TEXTJOIN skipping values you wanted. This page tells you why.

These aren't #REF! or #VALUE! — the formula ran successfully but the output is wrong. That's what makes them dangerous. A misconfigured SUMIFS can silently miscalculate revenue for months before someone notices. Each function has quirks that catch new users. This page catalogs them so you can spot the pattern in your own workbooks.

The four that fool the most people

SUMIFS

SUMIFS returning wrong total

Top causes: (1) sum_range and criteria_range don't have same dimensions, (2) criteria has trailing spaces, (3) using * or ? without wildcard-friendly criteria, (4) numeric criteria written as text ("5" vs 5), (5) date criteria in wrong format.

Fix: verify range shapes match. Use =SUM(criteria_range) to check row count. Wrap criteria with TRIM if from imported data.
Full SUMIFS error guide →
COUNTIF

COUNTIF counting wrong number

Cause: COUNTIF treats "" (empty string from formula) as non-blank. Wildcard characters (* and ?) always active — searching for literal * requires ~*. Case-insensitive so "abc" and "ABC" both match.

Fix: to count only truly blank use COUNTBLANK. To search literal wildcards, escape with tilde: =COUNTIF(range, "~*").
Full COUNTIF error guide →
SUMPRODUCT

SUMPRODUCT returns #VALUE!

Cause: One of the arrays contains text or errors. SUMPRODUCT multiplies element-by-element and errors propagate. Ranges of different sizes also break it — all arrays must be same dimensions.

Fix: wrap arrays in IFERROR: =SUMPRODUCT(IFERROR(A1:A10, 0), B1:B10). Or use SUMIFS instead for simple cases.
Full SUMPRODUCT error guide →
TEXTJOIN

TEXTJOIN skipping values or adding extras

Cause: Second argument (ignore_empty) is TRUE by default — blank cells skipped. If you want blank placeholders, set to FALSE. Also, TEXTJOIN can accept up to 252 arguments — passing more causes silent truncation.

Fix: to preserve blanks: =TEXTJOIN(", ", FALSE, range). For long lists, use a helper array/range instead of individual cell arguments.
Full TEXTJOIN error guide →

Browse errors by function family

Conditional Aggregation

5 errors

SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS gotchas.

SUMIFS wrong COUNTIF wrong AVERAGEIFS Wildcards

Text Functions

5 errors

TEXTJOIN, CONCAT, TEXTSPLIT, LEFT/RIGHT/MID, TEXT function issues.

TEXTJOIN TEXTSPLIT TEXT format LEFT/RIGHT length

Date/Time Functions

4 errors

DATEDIF hidden bugs, NETWORKDAYS weekend defaults, EOMONTH edge cases.

DATEDIF bug NETWORKDAYS EOMONTH

Array & Modern Functions

6 errors

SUMPRODUCT, LAMBDA, LET, FILTER, UNIQUE quirks.

SUMPRODUCT LAMBDA recursion LET scoping FILTER empty

The three universal function debugging tricks

  1. Formulas → Evaluate Formula — click through your formula step by step, seeing each intermediate value. Reveals which argument is going wrong.
  2. Select part of a formula in the formula bar → press F9 — Excel computes just that piece and shows the result. Great for testing what a criteria evaluates to, or what a range contains.
  3. Wrap suspicious pieces in test formulas — instead of writing a giant SUMIFS, put the criteria in a cell, put the criteria_range in a cell, and check each piece separately.

Prevention patterns

  • Use Excel Tables for source ranges: structured references (Sales[Revenue]) are self-documenting and hard to misconfigure
  • Test criteria explicitly: before writing SUMIFS, use =A1="target" in a helper cell to confirm what "target" actually matches
  • Prefer newer functions when available: XLOOKUP, FILTER, and IFS are less error-prone than their legacy alternatives
  • Comment complex formulas with LET: =LET(revenue, SUMIFS(...), profit_ratio, revenue/costs, profit_ratio) makes intent explicit
  • Validate totals against known good numbers: compare your SUMIFS output to a Pivot Table total on the same data
  • Test with tiny datasets first: a 10-row test set makes wrong results obvious. Scale up only after logic is verified.

Frequently asked questions

My SUMIFS returns 0 but the criteria clearly exists in the range — why?

Type mismatch. Your criteria might be a number but the range has text (or vice versa). Test with =SUMIF(range, "text_value") in quotes explicitly, then =SUMIF(range, 5) without quotes. One will work and reveal the actual data type in the range.

COUNTIF says my range has 100 non-blank cells but SUM shows 0 — what?

Those cells contain text or formulas returning "" (empty string). COUNTIF counts them as "non-blank" but SUM ignores non-numeric values. Use ISNUMBER to check individual cells or COUNT (not COUNTA) to only count numeric values.

Is DATEDIF really buggy?

Yes — DATEDIF has known bugs in the "MD" and "YD" units that Microsoft has never fixed. For year+month calculations, use =DATEDIF(A,B,"Y") & " years " & DATEDIF(A,B,"YM") & " months" — avoid MD entirely for critical calculations.

My TEXTJOIN result is truncated — why?

Excel cells have a 32,767 character limit. If TEXTJOIN produces a longer string, only the first 32K characters display. Check =LEN(cell) — if close to 32,767, split the join into multiple cells or aggregate at a higher level.

How do I know if a function has "wrong result" issues without hitting them?

Read the "Common errors and fixes" section on our individual function guide pages before using an unfamiliar function. Test with tiny datasets before scaling. And build validation checks that compare formula outputs to Pivot Table totals on the same data.

Wrong results, caught before they matter.

The Add-in's Formula Auditor spots common misconfigurations — mismatched ranges, wildcard-active criteria, dimension mismatches — before they propagate into reports.

Get the Add-in →