If your Excel SUMIF formula is not working, the most common causes are mismatched data types (text vs. number), using the wrong range sizes, or syntax errors like extra spaces or commas. This article walks through the exact fixes for the most frequent SUMIF failures, with step-by-step solutions you can apply right away.
What SUMIF Does and How It Works
SUMIF adds numbers in a range when a corresponding cell in another range meets a condition. The syntax is:
=SUMIF(range, criteria, [sum_range])
For example, with product names in A2:A20 and sales in B2:B20, to sum sales for “Widget”:
=SUMIF(A2:A20,"Widget",B2:B20)
If sum_range is omitted, Excel sums the cells in range itself. The function works in Excel for Microsoft 365, Excel 2021, 2019, and older versions, and on Mac and Excel for the web.
Why SUMIF Returns 0 (Zero) When It Shouldn’t
A result of 0 usually means no cells match the criteria. Check these three things first:
- Text vs. number: If your criteria is a number but the cells are stored as text (e.g., left-aligned by default), SUMIF won’t match. Use
=SUMIF(A2:A20,"123",B2:B20)if the numbers are text, or convert them to real numbers. - Extra spaces: A trailing space in a cell or in the criteria (like “Widget “) breaks the match. Use
=TRIM(A2)to clean data, or use wildcards:=SUMIF(A2:A20,"*Widget*",B2:B20). - Criteria with operators: To sum values greater than 100, you must use
">100"(quoted), not>100unquoted.
Criteria Syntax: Quotes, Operators, and Wildcards
Criteria can be a number, text, expression, or cell reference. Rules:
- Text and logical/arithmetic operators must be enclosed in double quotes:
">=10","Apples". - To reference a cell for the criteria, concatenate:
=SUMIF(A2:A20,">"&D1,B2:B20). - Wildcards:
*(any sequence),?(single character), and~to escape them. Example:=SUMIF(A2:A20,"A*",B2:B20)sums all starting with “A”.
Range Size Mismatch: A Silent Killer
If range and sum_range are different sizes, SUMIF uses the top-left cell of sum_range and adjusts automatically, but this can cause unexpected results. For clarity and accuracy, always make them the same size. For instance, if your data is in rows 2 to 100, use A2:A100 and B2:B100, not A2:A100 and B2:B99.
SUMIF vs. SUMIFS vs. SUMPRODUCT: Which to Use?
Use SUMIF for a single condition. Use SUMIFS for multiple conditions (note the argument order: SUMIFS(sum_range, criteria_range1, criteria1, ...)). SUMPRODUCT is more flexible but slower on large datasets. Example with two conditions (region in A, product in B, sales in C):
=SUMIFS(C2:C20,A2:A20,"North",B2:B20,"Widget")
Common Errors and How to Fix Them
- #NAME? – Usually a typo in the function name or missing quotes around text criteria.
- #VALUE! – Often caused by a criteria that is a formula error or a mismatch in data types.
- Wrong total (not 0 but incorrect) – Check for hidden rows, filtered data (SUMIF ignores hidden rows only if you use SUBTOTAL, not SUMIF), or merged cells.
- Criteria is a cell containing text – If D1 has “Widget”, use
=SUMIF(A2:A20,D1,B2:B20)– no quotes around D1.
Frequently Asked Questions
Why is my SUMIF returning 0 even though the values are there?
Most likely the criteria doesn’t match due to text vs. number format, extra spaces, or the range/sum_range sizes being off. Check by using =COUNTIF(A2:A20,"Widget") to see if any cells match the criteria at all.
How do I sum if a cell contains specific text?
Use wildcards: =SUMIF(A2:A20,"*Widget*",B2:B20) sums all rows where column A contains “Widget” anywhere. For case-insensitive matching, SUMIF is already case-insensitive.
Can I use SUMIF with dates?
Yes, but use a date serial number or a date function. For example, to sum sales after January 1, 2024: =SUMIF(A2:A20,">"&DATE(2024,1,1),B2:B20). Avoid typing dates as text like “1/1/2024” unless your cells are text.
Does SUMIF work with entire columns?
Yes, you can use A:A and B:B, but this can slow down calculations on large sheets. It’s better to use a defined range.
Why does my SUMIF ignore hidden rows?
SUMIF does not ignore hidden rows. If you need to sum only visible rows after filtering, use SUBTOTAL(109, range) or AGGREGATE instead.
How do I sum based on multiple criteria without SUMIFS?
You can use SUMPRODUCT: =SUMPRODUCT((A2:A20="North")*(B2:B20="Widget")*C2:C20). This works in all Excel versions.
Final Checklist Before You Give Up
- Ensure the criteria is typed exactly as it appears in the cell, including case (though SUMIF is case-insensitive, spaces matter).
- Verify that numbers are actually numbers (use
=ISNUMBER(A2)to test). - Check that you didn’t accidentally use a comma instead of a semicolon if your Excel uses semicolon as the list separator (common in European locales).
- If you’re using a table, try converting to a range or use structured references correctly.
Related
- Excel Power Query Web Connector: How to Import Data
- Excel Power Query Web Data: How to Import & Refresh
- Excel Power Query Weighted Average: How to Calculate
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Not Incrementing Cells When Dragged? How to FixIf Excel is not incrementing cells when you drag, it may be copying the selected value instead of…
- Microsoft Excel Page Breaks: How to Insert and Fix ThemA page break in Microsoft Excel marks where printed output moves to a new page. In Excel for…
- VLOOKUP Approximate Match Not Working? Fix the CauseVLOOKUP approximate match usually fails because the lookup column is not sorted in ascending order, the lookup value…
- Excel Ctrl+F Not Working? How to Fix Excel Find IssuesIf Ctrl+F is not working in Excel, first click a worksheet cell and press Ctrl+F again; on Mac,…
- Excel Date Filter Not Working? Causes and Fixes That WorkIf an Excel date filter is not working, first check whether the cells contain real Excel dates rather…
- Microsoft Excel Filter Not Working? Fix Common CausesIf a Microsoft Excel filter is not working, first select a cell in the data and choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.