Excel guide · 4 min read

SUMIF returning 0 or not working: causes and fixes

Quick answerSUMIF returns 0 when no row matches the criteria or when the numbers it should add are stored as text. Check three things: =ISTEXT() on a value in the sum range, =LEN() on a criteria cell for hidden spaces, and that operators are joined to cells with &, as in ">"&F2, not ">F2".

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	E	F
1	Rep	Region	Sales	Region	Min sale
2	Dana Ruiz	North	1250	North	1000
3	Mark Olsen	North	980
4	Jia Wen	South	1430
5	Ravi Menon	North	1105
6	Ellen Park	East	760
7	Tom Becker	South	875

Cause 1: the numbers are stored as text

SUMIF silently skips text in the sum range, even text that looks exactly like a number. Dana's 1250 in C2 came in from a CRM export as text. =SUMIF(B2:B7,"North",C2:C7) should be 3,335 but returns only 1,105. If every number in the column is text, the result is 0.

Confirm it with =ISTEXT(C2), or select the column and look at the status bar: if it shows Count but no Sum, the values are text. The permanent fix is to convert the column (select it, click the warning icon, Convert to Number, or Data > Text to Columns > Finish). If you cannot change the data, SUMPRODUCT with a double minus converts as it adds.

=SUMPRODUCT(--(B2:B7="North"),--C2:C7)

Result: 2355

--C2:C7 turns "1250" into a number, so Dana is counted: 1250 + 1105 = 2355. Mark is still missing, because of Cause 2.

Cause 2: a hidden space in the criteria range

B3 says "North " with a trailing space. SUMIF needs an exact match (ignoring case), so Mark's 980 is left out. =LEN(B3) returns 6 for a five-letter word, which gives it away.

Clean the column once: add =TRIM(B2) in a helper column, fill down, copy, and paste as values over the original. If the space is a non-breaking space from a web page, TRIM will not remove it; use =SUBSTITUTE(B2,CHAR(160),"") or Find and Replace. A wildcard such as "North*" also catches it, but it would catch "Northeast" too.

=SUMPRODUCT(--(TRIM(B2:B7)="North"),--C2:C7)

Result: 3335

TRIM removes the space and -- converts the text number, so all three North rows are added: 3335.

Cause 3: quotes in the wrong place

This one returns exactly 0. Criteria with an operator must be text, and a cell reference must sit outside the quotes, joined with &. ">F2" means "greater than the text F2", which no number is. The same mistake appears as =SUMIF(B2:B7,"E2",C2:C7), which looks for a region literally called E2.

Write ">"&F2 for a cell, ">1000" for a fixed number, and just E2 (no quotes) for an exact match to a cell. Dates work the same way: ">="&DATE(2026,11,1).

=SUMIF(C2:C7,">"&F2)

Result: 2535

With the operator joined to F2, sales over 1,000 add up to 2535 (1430 + 1105; Dana's text value is skipped).

Cause 4: arguments in the wrong order

SUMIF is (range, criteria, sum_range): where to look, what to look for, what to add. SUMIFS puts the sum range first: (sum_range, criteria_range, criteria). People switching between the two often swap them.

=SUMIF(C2:C7,"South",B2:B7) looks for "South" among the sales figures, finds nothing, and returns 0. Put the column with the labels first.

=SUMIF(B2:B7,"South",C2:C7)

Result: 2305

With the region column first, South adds up to 2305 (1430 + 875).

Cause 5: the ranges do not line up

SUMIF does not check that the two ranges match. It takes the top-left cell of the sum range and resizes it to the shape of the criteria range. If the sum range starts one row off, every match adds the value from the wrong row, with no error.

=SUMIF(B2:B7,"South",C1:C6) returns 1,740: the South rows are 4 and 7, but it adds C3 and C6. Always start both ranges on the same row. SUMIFS is stricter: ranges of different sizes give #VALUE!, which is one reason to prefer it.

=SUMIF(B2:B7,"South",C1:C6)

Result: 1740

The shifted range adds Mark's 980 and Ellen's 760 to give 1740 instead of the correct 2305.

Cause 6: Excel is not recalculating

If SUMIF shows an old result after you change the data, calculation may be set to manual, often because a workbook you opened earlier set it that way. Check Formulas > Calculation Options and choose Automatic, or press F9 to recalculate now.

If the cell shows the formula itself instead of a result, the cell was formatted as Text before you typed it. See the guide on formulas showing as text.

Common mistakes

Patching formulas instead of cleaning data

Convert text numbers and TRIM labels once at the source. Every SUMIF, pivot table and chart then works.

Putting a cell reference inside quotes

"E2" is text. Use E2 for a match, or ">"&E2 with an operator.

Copying SUMIF down without $ signs

Lock both ranges ($B$2:$B$7, $C$2:$C$7) or they slide down and drop rows.

FAQ

Why is my SUMIF returning 0?

Either no cell matches the criteria exactly (often because of extra spaces or a wrongly quoted cell reference), or the values in the sum range are text. Check with =ISTEXT() and =LEN().

Does SUMIF work with numbers stored as text?

Not in the sum range: text values are skipped. Convert them with Convert to Number or Text to Columns, or use SUMPRODUCT with -- to convert while adding.

Is SUMIF case-sensitive?

No. "north" and "North" match. For a case-sensitive total, use SUMPRODUCT with the EXACT function.

Why does SUMIF return #VALUE!?

Usually because it refers to another workbook that is closed. SUMIF and SUMIFS need the source workbook open; open it, or use SUMPRODUCT, which works with closed workbooks.

Functions used

Related guides

Updated November 2, 2026