SUMIF returns 0 or the wrong total in Excel: the fixes

Symptom                                   Documented cause
0 instead of the expected result          text criteria not in quotation marks (SUMIFS)
total too low or too high, no error       sum_range a different size from range (SUMIF)
#VALUE!                                   range in a closed workbook
#VALUE!                                   criteria string longer than 255 characters
#VALUE!                                   SUMIFS ranges of different sizes
#VALUE!  =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)

Why it happens

The SUMIF function has three arguments, SUMIF(range, criteria, [sum_range]). It tests each cell in range against criteria and adds the matching cells in sum_range. Each of the documented rules below, when broken, changes what gets added.

• Criteria must be quoted when they are text or contain an operator. Microsoft's SUMIF page states: "Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks". The SUMIFS function page lists, under Common Problems, "0 (Zero) is shown instead of the expected result" and gives the fix: make sure the criteria "are in quotation marks if you are testing for text values". Numeric criteria such as 300000 do not need quotes.
• An operator combined with a cell is built with &. Microsoft's own example is =SUMIF(A2:A5,">" & C2,B2:B5), described as the sum of commissions "for property values greater than the value in C2". The cell reference sits outside the quotes.
• Wildcards are active in text criteria. In criteria, ? matches any single character and * matches any sequence of characters. To match a real question mark or asterisk you type a tilde (~) in front of it. A criterion that contains a literal * or ? therefore matches more cells than you meant unless you escape it.
• Numbers stored as text. Microsoft's conversion guide warns that "Numbers that are stored as text can cause unexpected results", and the Top ten ways to clean your data page says numbers stored as text can "cause problems with calculations", and names the case where "the number was inadvertently imported as text".
• Hidden spaces and nonprinting characters. The same clean-up page says text values can contain leading, trailing or embedded space characters (Unicode 32 and 160) and nonprinting characters that "can sometimes cause unexpected results when you sort, filter, or search". A criteria cell that reads North with a trailing space is not the same text as North.
• sum_range of a different size. The SUMIF page says sum_range "should be the same size and shape as range". If it isn't, "performance may suffer, and the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range". Its example: with range A1:A5 and sum_range B1:K5, the cells actually summed are B1:B5. No error appears, so a wrong sum_range silently gives a wrong total.
• TRUE and FALSE in sum_range. SUMIFS notes that cells in sum_range containing TRUE evaluate to 1 and those containing FALSE evaluate to 0.
• Long strings. SUMIF "returns incorrect results when you use it to match strings longer than 255 characters or to the string #VALUE!".
• Argument order. sum_range is the first argument in SUMIFS and the third in SUMIF. Microsoft calls this "a common source of problems using these functions", for instance when you copy a SUMIF and turn it into a SUMIFS without moving the ranges.

How to fix it

Work through these in order. Each step targets one cause above.

1. Check the argument order. SUMIF takes the condition range first and the range to add last; SUMIFS takes the range to add first.

   =SUMIF(B2:B5,"John",C2:C5)
   =SUMIFS(C2:C5,B2:B5,"John")
   

   Both formulas sum C2:C5 where B2:B5 equals John.

2. Quote the criterion correctly. Text goes in double quotation marks. An operator with a fixed value goes inside the quotes. An operator with a cell goes in quotes and the cell is joined with &.

   =SUMIF(A2:A7,"Fruits",C2:C7)
   =SUMIF(A2:A5,">160000",B2:B5)
   =SUMIF(A2:A5,">"&C2,B2:B5)
   =SUMIF(A2:A5,300000,B2:B5)
   

   If the criterion is simply a cell holding the value to match, reference it with no quotes at all: =SUMIF(A2:A7,E2,C2:C7).

3. Check that the cells you add are numbers. In a spare column, test the first cell of sum_range and the matching cell of range:

   =ISNUMBER(C2)
   =ISTEXT(C2)
   =ISNUMBER(A2)
   

   The IS functions do not convert their argument, so a number stored as text returns FALSE from ISNUMBER and TRUE from ISTEXT. Fill the test down to find every text cell.

4. Convert numbers stored as text. Select the cells, select the error indicator in the top-left corner of the cell (or press Alt+Shift+F10), then select Convert to Number. If no indicator appears, Microsoft says to go to File > Options > Formulas and, under Error Checking, select Enable background error checking. Otherwise convert in a helper column and paste the results back as values:

   =VALUE(C2)
   

   Fill it down, copy the helper column, then on the Home tab select the arrow below Paste > Paste Special > Values over the original column.

5. Make the criterion and the criteria column the same type. If the criteria column holds codes such as 1001 stored as text while your criterion is the number 1001, or the other way round, convert one side so both match, using step 4. Microsoft's pages do not spell out how SUMIF compares a text "1001" with the number 1001, so do not rely on it either way; make the types identical.

6. Remove hidden spaces from the criteria column. TRIM removes the ordinary space (value 32) but, as Microsoft's TRIM documentation notes, it does not by itself remove the nonbreaking space (value 160). Replace the nonbreaking space first, then trim, in a helper column:

   =TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))
   

   UNICHAR returns the Unicode character for the number you give it. Paste the cleaned column back as values over column A, or point SUMIF's range at the helper column. Trim the criterion cell too if it was typed or pasted with a trailing space.

7. Escape literal wildcards. If you need to match text that really contains * or ?, put ~ before it:

   =SUMIF(B2:B20,"Size~?",C2:C20)
   

8. Make the ranges the same size. Give range and sum_range the same number of rows and columns, and lock them before filling the formula down:

   =SUMIF($A$2:$A$100,E2,$C$2:$C$100)
   

9. Look for TRUE or FALSE in sum_range. If the column you add contains logical values, they count as 1 and 0. Remove them from the column or point sum_range at a column that holds only numbers.

10. Split criteria longer than 255 characters. Microsoft's fix is to shorten the string or join it from parts with & or CONCATENATE:

    =SUMIF(B2:B12,"long string"&"another long string")
    

Check that it worked

• ISNUMBER returns TRUE for every cell in sum_range you expect to be added.
• The cleaned criteria cells and your criterion are identical text: no stray spaces, and the same type on both sides.
• To confirm the formula pattern itself, paste Microsoft's Example 1 data (Property Value in A1:A5, Commission in B1:B5: 100,000/7,000, 200,000/14,000, 300,000/21,000, 400,000/28,000, with 250,000 in C2) into a blank sheet. Microsoft lists =SUMIF(A2:A5,">160000",B2:B5) as 63,000 and =SUMIF(A2:A5,">"&C2,B2:B5) as 49,000. If your copy gives those numbers, the syntax is right and any remaining problem is in your own data.

SUMIF or SUMIFS returns #VALUE! instead of 0

A #VALUE! from these functions has its own Microsoft troubleshooting page, which lists three causes:

• The formula refers to a closed workbook. "SUMIF/SUMIFS functions that refer to a cell or a range in a closed workbook will result in a #VALUE! error." Microsoft notes the same issue affects COUNTIF, COUNTIFS and COUNTBLANK. The fix is to open the workbook named in the formula and press F9 to refresh. Microsoft also points to a workaround article that uses SUM and IF together in an array formula.
• The criteria string is longer than 255 characters. Split it with & as in step 10.
• In SUMIFS, criteria_range and sum_range are different sizes. Microsoft's example =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14) returns #VALUE! because C2:C10 does not match A2:A12 and B2:B12; changing sum_range to C2:C12 fixes it. The same page adds: "SUMIF can use different size ranges", which is why SUMIF does not error in that situation but adds the shifted range described above.

For a #VALUE! that comes from somewhere other than SUMIF, such as text in arithmetic or a date stored as text, see #VALUE! error in Excel: find the bad cell and fix it. If the cell shows the formula itself rather than a number, or the total does not update after you change the data, that is a calculation setting rather than a SUMIF problem: see Excel formula not calculating or showing as text.

The same hidden spaces and numbers stored as text that make SUMIF return 0 also make lookups fail, as covered in XLOOKUP returns #N/A when the value exists and VLOOKUP returns #N/A or the wrong value. Once the criterion is quoted the way Microsoft documents it, the ranges are the same size, and ISNUMBER agrees with what you see on screen, SUMIF adds what you expect.