If your Excel SUMIF formula returns 0 when it should return a number, the most common causes are mismatched criteria, text-formatted numbers, or referencing the wrong range. Start by checking that your criteria range and sum range are aligned, then verify that the values you’re summing are stored as numbers, not text.
Why SUMIF Returns 0: The Core Mechanics
SUMIF adds up values in a range that meet a condition you specify. Its syntax is:
=SUMIF(range, criteria, [sum_range])
Here’s what each part does:
- range – the cells you want to check against the criteria.
- criteria – the condition (e.g., “>10”, “Apple”, “*ed”).
- sum_range – the actual cells to add. If omitted, Excel sums the range itself.
For example, with items in A2:A10 and quantities in B2:B10, =SUMIF(A2:A10, "Apple", B2:B10) adds all quantities in column B where column A says “Apple”. If you see 0, the formula is running, but no cells meet the criteria or the sum range is empty.
Check 1: Criteria Mismatch (Text vs. Numbers)
The most frequent culprit is a criteria that doesn’t exactly match the cell contents. Excel treats text and numbers differently:
- If your criteria is
"Apple"but the cell containsapple(lowercase), SUMIF is case-insensitive, so that works. But if the cell has a leading or trailing space (e.g.," Apple"), it won’t match. - If your criteria is
"100"(text) but the cell contains the number 100, it may fail. Use100without quotes for numbers, or">=100"for comparisons. - Wildcards:
*matches any sequence,?matches a single character. For example,"*ed"matches “red”, “bed”, “shed”.
Fix: Use =SUMIF(A2:A10, "Apple", B2:B10) and ensure no extra spaces. To trim spaces, use =SUMIF(A2:A10, TRIM("Apple"), B2:B10) or clean the data with TRIM.
Check 2: Numbers Stored as Text
If your sum range contains numbers that are actually text (e.g., imported from a CSV or typed with an apostrophe), SUMIF will ignore them. You’ll see 0 even if the criteria matches.
How to test: Select the sum range and look at the Status Bar (bottom right). If it shows “Count” but not “Sum”, the values are text.
Fix: Select the range, click the warning icon, and choose “Convert to Number”. Or use VALUE in a helper column: =VALUE(B2) and sum that column.
Check 3: Wrong Range References
A classic error is using the wrong ranges. For instance, if your criteria range is A2:A10 but your sum range is B2:B10, they must be the same size. If you accidentally use B2:B9, the last row is ignored, and if the matching value is in that row, you get 0.
Fix: Ensure both ranges have the same number of rows and start at the same row. Use absolute references (e.g., $A$2:$A$10) if you copy the formula.
Check 4: Criteria That Includes Operators
When using comparison operators (>, <, =, <>), you must enclose the entire criteria in quotes. For example:
=SUMIF(B2:B10, ">10")– sums values greater than 10.=SUMIF(B2:B10, "<>", C2:C10)– sums non-empty cells in C where B is not empty.
If you write =SUMIF(B2:B10, >10) without quotes, Excel throws an error, not 0. But if you use a cell reference for the criteria, like =SUMIF(A2:A10, D1, B2:B10), and D1 contains ">10" as text, it works. If D1 contains a number, it treats it as an exact match.
Check 5: Hidden Characters or Non-Printable Characters
Imported data may contain non-breaking spaces or line breaks that make text look identical but not match. Use CLEAN to remove non-printable characters and TRIM to remove extra spaces.
Example: =SUMIF(A2:A10, TRIM(CLEAN("Apple")), B2:B10) – but this only helps if the criteria is a constant. Better to clean the data first: =TRIM(CLEAN(A2)) in a helper column, then use that column as the criteria range.
Alternatives: SUMIFS, SUMPRODUCT, and Dynamic Arrays
If SUMIF still returns 0, consider these alternatives:
- SUMIFS – allows multiple criteria. Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, ...). For example,=SUMIFS(B2:B10, A2:A10, "Apple", C2:C10, ">5"). - SUMPRODUCT – can handle arrays and text-to-number conversion. For example,
=SUMPRODUCT((A2:A10="Apple")*(B2:B10))– but this requires B2:B10 to be numeric. To coerce text numbers, use=SUMPRODUCT((A2:A10="Apple")*(VALUE(B2:B10)))– note that this will error if any cell is not numeric. - FILTER + SUM – in Excel 365, you can do
=SUM(FILTER(B2:B10, A2:A10="Apple")). This is more flexible and ignores errors if you wrap it properly.
Frequently Asked Questions
Why does SUMIF return 0 when the criteria is a date?
Dates are stored as serial numbers in Excel. If you type a date as text (e.g., "1/1/2023"), it may not match. Use DATE(2023,1,1) or a cell reference containing a real date. For example, =SUMIF(A2:A10, DATE(2023,1,1), B2:B10).
Can SUMIF sum cells that contain text numbers?
No, SUMIF ignores text in the sum range. Convert text to numbers using the methods above, or use SUMPRODUCT with VALUE.
Why does SUMIF return 0 when the criteria is a cell reference?
If the cell reference contains a number, SUMIF treats it as an exact match. If it contains text, it matches text. Ensure the cell has no extra spaces or formatting. Also, if the criteria cell is blank, SUMIF will match blank cells in the range, which may sum to 0.
Does SUMIF work with wildcards?
Yes, you can use * and ? in the criteria. For example, =SUMIF(A2:A10, "*Apple*", B2:B10) sums all cells containing "Apple". To treat * as a literal, use ~*.
What if SUMIF returns 0 but there are visible numbers?
Check if the cells are formatted as text (left-aligned by default). Also, check if the formula is in a cell that is formatted as text – that would show the formula as text, not 0. If the formula is correct, use the Evaluate Formula tool (Formulas > Evaluate Formula) to step through it.
If none of these checks solve the problem, try rebuilding the formula from scratch or using the SUMIFS function, which is more robust in many cases.
Related
- Excel Power Query: Merge Rows from Columns or Tables
- Excel Power Query Merge Two Queries: Step-by-Step
- Excel Power Query: Merge 3 Tables
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Cell Pull-Down Lists: Create, Edit, and Fix ThemTo create an Excel cell pull-down list in Microsoft 365 for Windows, select the cell, choose Data >…
- Can’t Insert a Row in an Excel Table? Fix ItIf you can’t insert a row in an Excel table, first click a cell inside the table, right-click…
- Duplicate Values Not Working in Excel? How to Fix ItIf Excel is not highlighting duplicate values, first select the full range you want to check, then choose…
- Excel Cannot Open the File: How to Fix a Corrupt XLSXYes—Excel may be unable to open an XLSX when its workbook file is damaged, incomplete, or not really…
- Excel Page Break Not Going Away? Fix Persistent LinesIf an Excel page break will not go away, first switch to View > Normal; if the line…
- Excel Dates Sorting A-Z: Why It Happens and How to FixExcel sorts real dates A to Z in chronological order, from oldest to newest. If the dates look…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.