SUMIFS Not Working? Why It Returns 0 and How to Fix It

By the XLsheetAI Team · Updated October 15, 2026 · 12 min read

The short answer

SUMIFS is not working because at least one condition matches nothing, so Excel adds up zero rows and shows 0. Usually the data and the criteria look identical on screen but are not the same to Excel: a number stored as text, a date stored as text, a space you cannot see, or a criteria like ">=A1" that compares against the literal letters "A1". A #VALUE! error is a different problem, and it nearly always comes down to ranges of different sizes or a link to a closed workbook. The table below matches each symptom to its cause, and every cause has its own section with a broken formula and the fixed one.

All examples use one layout: Region in column A, Date in column B, Amount in column C, data in rows 2 to 500.

Quick diagnosis: symptom, cause, fix

Cause 1: numbers stored as text

This is the classic SUMIFS returning 0. The formula is fine, the conditions match, but the amounts in the sum range are text that happens to look like numbers. Microsoft's SUMIF documentation says it plainly: blank and text values are ignored. Every matching row contributes nothing, so the total is 0.

Looks right, returns 0: =SUMIFS(C2:C500, A2:A500, "East")

Signs the amounts are text: they sit on the left of the cell, there is a small green triangle in the corner, =ISTEXT(C2) returns TRUE, or =COUNT(C2:C500) comes back lower than the number of rows. Data exported from another system or pasted from a web page is the usual source.

Fix: select the column, click the warning icon and choose Convert to Number, as Microsoft describes in Convert numbers stored as text to numbers. Or add a helper column with =VALUE(C2) and point SUMIFS at that. Then the same formula works: =SUMIFS(C2:C500, A2:A500, "East").

If you cannot change the data, a formula that forces the text into numbers gets the right total: =SUMPRODUCT((A2:A500="East")*C2:C500). Multiplying turns "120" into 120. It returns #VALUE! if any amount is real text such as "n/a", so converting the column is still the better fix.

The same problem can sit in a criteria range. A condition like ">100" is a number comparison, so numbers stored as text in that column will not pass it.

Cause 2: dates stored as text, or the date criteria written wrong

"SUMIFS with dates" goes wrong in three separate ways, and they all show 0.

The cell reference is inside the quotes. Everything between quotes is text, so this compares every date against the two characters "A1":

Broken: =SUMIFS(C2:C500, B2:B500, ">=A1")
Fixed: =SUMIFS(C2:C500, B2:B500, ">="&A1)

The operator goes in quotes and the cell joins it with an ampersand. The SUMIF page states the rule: criteria that include logical or mathematical symbols must be in double quotes. The cell reference must stay outside them.

The date is typed as text. ">=10/1/2026" can work, but Excel reads it using the computer's regional date order, so the same file can mean October 1 on one machine and January 10 on another. Build the date with DATE instead:

Risky: =SUMIFS(C2:C500, B2:B500, ">=10/1/2026")
Safe: =SUMIFS(C2:C500, B2:B500, ">="&DATE(2026,10,1))

For a whole month, use a start date and the first day of the next month. Using < on the next month, instead of <= on the last day, also catches dates that carry a time such as 14:30:

=SUMIFS(C2:C500, B2:B500, ">="&DATE(2026,10,1), B2:B500, "<"&DATE(2026,11,1))

The time part is also why =SUMIFS(C2:C500, B2:B500, DATE(2026,10,15)) can miss rows: a timestamp of October 15 at 09:00 is not equal to October 15 at midnight.

The dates in the column are text. If =ISNUMBER(B2) returns FALSE, the "date" is a string, and a date comparison will skip it. Text dates also sit on the left side of the cell. Convert them with a helper column of =DATEVALUE(B2), following Microsoft's Convert dates stored as text to dates, and point the formula at the helper column.

Cause 3: criteria ranges are not the same size (#VALUE!)

This is the most common SUMIFS #VALUE!. Every range in SUMIFS must have the same number of rows and columns. The SUMIFS documentation requires each criteria range to match the sum range, and Microsoft's page on fixing #VALUE! in SUMIF/SUMIFS lists the mismatch as a cause of the error.

Broken: =SUMIFS(C2:C500, A2:A500, "East", B2:B450, ">="&DATE(2026,1,1))
Fixed: =SUMIFS(C2:C500, A2:A500, "East", B2:B500, ">="&DATE(2026,1,1))

This catches people moving from SUMIF, because SUMIF does not complain. Given a sum range of a different size, SUMIF quietly uses a range the size of the criteria range, starting at the first cell of the sum range. SUMIFS refuses. AVERAGEIFS and COUNTIFS have the same rule.

The easy way to stop this happening again is to turn the data into an Excel Table (Ctrl+T) and use column names such as Sales[Amount]. Every column of a Table is always the same length, and it grows when you add rows.

Cause 4: arguments in SUMIF order

SUMIF and SUMIFS put the sum range in different places. In SUMIF it is the last, optional argument: SUMIF(range, criteria, [sum_range]). In SUMIFS it comes first: SUMIFS(sum_range, criteria_range1, criteria1, ...). Microsoft's SUMIFS page calls this out as the first thing to understand about the two functions.

Mix them up and the formula usually still runs, it just adds the wrong column:

Broken: =SUMIFS(A2:A500, C2:C500, "East")
Fixed: =SUMIFS(C2:C500, A2:A500, "East")

The broken one looks for "East" in the Amount column, finds nothing, and would have added region names anyway, so it returns 0. The same order applies to AVERAGEIFS, where the average range comes first. COUNTIFS has no sum range, so it is only range and criteria pairs.

Cause 5: trailing spaces and non-breaking spaces

"East " with a space at the end is not "East". Neither is "East" followed by a non-breaking space, which is common in data copied from web pages and some exports. Both look perfect on screen and both make an exact text match fail. Microsoft's COUNTIF guidance warns about leading spaces, trailing spaces, straight versus curly quotes and nonprinting characters for exactly this reason.

Quick ways to check:

• =LEN(A2) returns 5 for a cell that should say "East" (4 letters).
• =CODE(RIGHT(A2)) returns 32 for a normal space and 160 for a non-breaking space.
• Change the criteria to "East*". If a total appears, something is hiding after the word.

Broken: =SUMIFS(C2:C500, A2:A500, "East") against cells that contain "East "
Fix: clean the column in a helper column, then sum against it: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

The SUBSTITUTE is needed because, as the TRIM documentation notes, TRIM on its own does not remove the non-breaking space (character 160). SUBSTITUTE turns it into a normal space, CLEAN strips nonprinting characters, and TRIM removes the spaces at the ends. Check the criteria cell too: a trailing space in the cell you type "East" into breaks the match just as well.

Case does not matter. SUMIFS and COUNTIFS treat "east" and "EAST" as the same, so capital letters are never the reason for a 0. For a bigger cleanup, see how to clean messy data with AI.

Cause 6: wildcards * ? and the ~ escape

In SUMIFS criteria, * matches any run of characters and ? matches one character. That is useful: "*East*" matches "North East" and "East Coast", and "Q?-2026" matches Q1-2026 to Q4-2026. Join a cell into a wildcard with "*"&E1&"*".

It goes wrong when the data itself contains a star or question mark, such as product codes. Then the total comes out too high, not too low:

Broken: =SUMIFS(C2:C500, A2:A500, "AB*12") also matches AB-0012, ABX12 and anything else that starts with AB and ends with 12
Fixed: =SUMIFS(C2:C500, A2:A500, "AB~*12")

The tilde tells Excel the next character is a real star or question mark. Microsoft documents this on the SUMIFS and COUNTIFS pages. Wildcards only work on text, so "1*" will not match a cell holding the number 123.

Cause 7: you need OR, but SUMIFS only does AND

"SUMIFS multiple criteria" means every condition must be true on the same row. Microsoft's COUNTIFS page describes it that way: a row counts only when all of its cells meet their criteria. So two values for the same column can never both be true:

Broken: =SUMIFS(C2:C500, A2:A500, "East", A2:A500, "West") returns 0, because no region is both
Fixed: =SUM(SUMIFS(C2:C500, A2:A500, {"East","West"}))
Also fixed: =SUMIFS(C2:C500, A2:A500, "East") + SUMIFS(C2:C500, A2:A500, "West")

The array constant makes SUMIFS run once for each value, and SUM adds the results, as Exceljet's walkthrough shows. The values can come from cells instead: =SUM(SUMIFS(C2:C500, A2:A500, E1:E2)). In current Excel this works with a normal Enter; very old versions may need Ctrl+Shift+Enter for the range version.

Be careful with OR across two different columns, such as "region is East or the rep is Ana" (rep in column D). Adding two SUMIFS counts a row twice when it matches both. Use SUMPRODUCT, which only counts each row once:

=SUMPRODUCT(((A2:A500="East")+(D2:D500="Ana")>0)*C2:C500)

For matching on several columns when you need a value back rather than a total, see lookups with multiple criteria.

Cause 8: the formula points at a closed workbook

SUMIFS that reads from another file works while that file is open. Close it, let the sheet recalculate, and the result turns into #VALUE!. Microsoft lists this as a known limit of SUMIF and SUMIFS, and of COUNTIF and COUNTIFS too: they need the source workbook open.

Breaks when Sales.xlsx is closed: =SUMIFS([Sales.xlsx]Data!$C$2:$C$500, [Sales.xlsx]Data!$A$2:$A$500, "East")

Fix 1: open the source workbook and press F9, which is Microsoft's own advice.
Fix 2: use a formula that does not need the file open. Microsoft suggests SUM with IF as an array formula: =SUM(IF([Sales.xlsx]Data!$A$2:$A$500="East", [Sales.xlsx]Data!$C$2:$C$500, 0)). SUMPRODUCT does the same job and is easy to read: =SUMPRODUCT(([Sales.xlsx]Data!$A$2:$A$500="East")*[Sales.xlsx]Data!$C$2:$C$500)

Once the file is closed Excel shows the full folder path inside the formula, so it will look longer than this. If the report depends on another file every day, bringing that data into the report workbook (with Power Query or a plain copy) is sturdier than any link.

Cause 9: calculation is set to Manual

If SUMIFS shows an old number that does not change when you edit the data, the formula may be fine and Excel is just not recalculating. When the workbook calculation setting is Manual, formulas only update when you ask, as Microsoft explains in Change formula recalculation.

Fix: go to Formulas, Calculation Options, and pick Automatic. To refresh once without changing the setting, press F9. This one is easy to pick up by accident from a large workbook someone set to Manual to keep it fast.

Cause 10: comparison operators, blanks and empty criteria cells

Any criteria with >, <, = or <> must be text in quotes, with any cell or function joined on with &. A plain number such as 100 needs no quotes. Here are the patterns people get wrong most:

For example, to add the amounts where column D has any note in it: =SUMIFS(C2:C500, D2:D500, "<>").

One more trap: when the criteria points at an empty cell, it is not "match everything". The COUNTIFS documentation says an empty criteria cell is treated as 0, so the formula looks for zeros and usually returns 0. If the criteria comes from a filter cell that is sometimes left blank, handle that case yourself:

Broken when E1 is empty: =SUMIFS(C2:C500, A2:A500, E1)
Fixed: =IF(E1="", SUM(C2:C500), SUMIFS(C2:C500, A2:A500, E1))

COUNTIFS and AVERAGEIFS break the same way

"COUNTIFS not working" has the same list of causes as SUMIFS, apart from text numbers in a sum range, since COUNTIFS has no sum range. Ranges of different sizes, closed workbooks, hidden spaces, wildcards, AND-only logic and quoted operators all apply exactly as above.

AVERAGEIFS has one difference worth knowing. When no row matches, it does not return 0. The AVERAGEIFS documentation says it returns #DIV/0!, and it does the same when the average range is blank or text. So a #DIV/0! from AVERAGEIFS is the same "nothing matched" signal that SUMIFS shows as 0, and you can debug it with the same table. If you want a blank instead, wrap it: =IFERROR(AVERAGEIFS(C2:C500, A2:A500, "East"), ""), but only after you have checked the match really should be empty.

Not sure whether you need a sum, a count or an average in the first place? SUM vs COUNT vs AVERAGE covers the difference.

Stuck on a formula that returns 0?

Paste it into XLsheetAI with a line about what you expected, and get the fixed formula and a plain explanation of what was wrong. You can also describe the total you want in your own words and let it write the SUMIFS for you.

Keep going: the Excel formulas cheat sheet has SUMIFS next to the other functions you will use with it, lookup with multiple criteria covers returning a value instead of a total, and cleaning messy data fixes the text numbers and stray spaces behind most of these problems.