Before diving in: if SUMIF returns 0, the most likely cause is numbers stored as text (reason 1) or a range size mismatch (reason 2). If it returns #VALUE!, your criteria range and sum range are different sizes or a criteria is malformed. If it returns the wrong non-zero number, check wildcards (reason 4) and spaces (reason 5).
Numbers Stored as Text (Most Common)
If your criteria range contains numbers formatted as text (left-aligned, green triangle in corner), SUMIF cannot match them against a numeric criteria. =SUMIF(A:A, 100, B:B) returns 0 if the values in column A are text "100" rather than the number 100.
Fix: Select the criteria range → click the yellow warning icon → "Convert to Number". Or use Paste Special → Multiply by 1 to bulk-convert. Once the range contains real numbers, SUMIF will match correctly.
Criteria Range and Sum Range Are Different Sizes
SUMIF requires the criteria_range and sum_range to be the same size. If criteria_range is A1:A100 but sum_range is B1:B50, SUMIF will only sum the first 50 rows and silently ignore the rest.
Fix: Make both ranges the same size. The safest approach is to use full column references: =SUMIF(A:A, "Target", B:B). Full column references automatically include all current and future rows.
Criteria Text Is Not in Quotes
Text criteria must be wrapped in double quotes. =SUMIF(A:A, London, B:B) will not work — Excel tries to interpret "London" as a named range, fails, and returns 0 or #NAME?.
Fix: Always quote text criteria: =SUMIF(A:A, "London", B:B). Number criteria do not need quotes: =SUMIF(A:A, 100, B:B) is correct. Comparison operators must also be inside quotes: =SUMIF(A:A, ">100", B:B).
Wildcard Characters Used Incorrectly
SUMIF supports wildcards: * (any characters) and ? (single character). Common mistakes include: using * without understanding it matches any position, or forgetting that wildcards only work with text — they do not work for numeric criteria.
Fix: To match cells containing "London" anywhere: =SUMIF(A:A, "*London*", B:B). To match cells starting with "Lon": =SUMIF(A:A, "Lon*", B:B). To match exactly "London" (no wildcards): =SUMIF(A:A, "London", B:B). For numbers, drop wildcards: =SUMIF(A:A, ">100", B:B).
Extra Spaces in the Criteria Range
Invisible leading or trailing spaces cause SUMIF to miss matches. "London " (with a trailing space) does not equal "London". This is very common in data imported from other systems.
Fix: Use TRIM to clean the criteria before comparing. The cleanest solution is to add a helper column: =TRIM(A1) copied down, then use that column as your criteria_range. Alternatively, you can use SUMPRODUCT for a space-tolerant version: =SUMPRODUCT((TRIM(A1:A100)="London")*B1:B100).
Case Sensitivity Expectation
SUMIF is not case-sensitive. =SUMIF(A:A, "london", B:B) and =SUMIF(A:A, "LONDON", B:B) return the same result. If you need case-sensitive matching, SUMIF cannot do it.
Fix: Use SUMPRODUCT with EXACT: =SUMPRODUCT((EXACT(A1:A100, "London"))*B1:B100). EXACT is case-sensitive and returns TRUE only for exact case matches.
SUMIF vs SUMIFS — When to Use Which
Use SUMIF when you have one condition: =SUMIF(range, criteria, sum_range)
Use SUMIFS when you have multiple conditions: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
Note that the argument order is different — SUMIFS puts the sum_range first, SUMIF puts it last. Mixing these up is a common mistake when switching between the two functions.
SUMPRODUCT as a Fallback
When SUMIF is giving you trouble with wildcards, spaces, or case sensitivity, SUMPRODUCT is a reliable fallback:
=SUMPRODUCT((criteria_range="value")*sum_range)SUMPRODUCT evaluates each condition cell by cell, giving you more control. It is slightly slower on very large datasets but works correctly in cases where SUMIF behaves unexpectedly.
Frequently Asked Questions
Why does SUMIF return 0 for a date criteria?
Dates in SUMIF criteria must be formatted correctly. Use =SUMIF(A:A, DATE(2025,1,1), B:B) rather than typing a date string as criteria. Text date strings like "01/01/2025" may not match depending on your regional settings.
Can SUMIF sum across multiple sheets?
No — SUMIF cannot natively sum across multiple sheets. Use SUMPRODUCT with INDIRECT to build a cross-sheet sum, or consolidate your data into one sheet first.
SUMIF is very slow on large datasets — how do I speed it up?
Avoid using full column references (A:A) on datasets with many rows — it forces Excel to check every cell in the column. Use a defined range instead: A1:A10000. Also consider converting the data range to an Excel Table, which can improve calculation performance.