Excel SUMIF Not Working? 7 Fixes to Try

Errors & fixes · Updated November 22, 2026

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 >100 unquoted.

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.