SUMIF 或 SUMIFS 能看到匹配行卻返回 0

You can see the rows that match, the amounts are sitting right next to them, and SUMIFS still says 0. Most often the amounts are text that only look like numbers. SUMIF and SUMIFS skip text in the sum range, so they return 0 without throwing an error.

The quickest way to confirm it is to compare two counts on the amount column:

=COUNT(D2:D9) counts real numbers, and =COUNTA(D2:D9) counts anything that isn’t blank. If COUNT comes back lower, some of the amounts are text.

If you can’t add helper columns, this one formula adds them anyway, because multiplying converts the text to numbers:

=SUMPRODUCT((TRIM(B2:B9)="Greenline")*(C2:C9="Paid")*D2:D9)

On the sample below it returns 1,545, while the plain SUMIFS returns 0. The rest of this page covers the helper-column fix I’d use on imported data, plus the other three things that produce a 0: a criterion that doesn’t quite match, a date criterion written as text, and two criteria on the same column.

The sample: eight payments, two hidden problems

Type this into A1:D9. In D2, D4, D6 and D8, enter the amounts with a leading apostrophe ('450) so they’re stored as text. In B6, type Greenline followed by one space. Enter the dates as real dates.

Row 6’s customer looks identical to the others; the cell is Greenline plus a trailing space. Greenline’s paid total should be 450 + 790 + 180 + 125 = 1,545. This formula returns 0:

=SUMIFS(D2:D9,B2:B9,"Greenline",C2:C9,"Paid")

The three matching rows it does find (2, 4 and 8) all hold text amounts. Row 6 isn’t matched at all because of the space. The text amounts alone are enough to produce the 0, and once they’re fixed the space would still leave you 180 short.

Amounts that are text

=COUNT(D2:D9) returns 4 and =COUNTA(D2:D9) returns 8, so half the amounts are text. You can test a single cell with =ISNUMBER(D2), which returns FALSE here even though D2 shows 450. Changing the cell format to Number doesn’t fix it; the value stays text until it’s re-entered or converted.

For data you re-import every week, I’d add a helper column rather than convert the source by hand each time. In E2, fill down to E9:

=IF(D2="","",VALUE(D2))

E2 now holds the number 450. The IF keeps empty source cells empty instead of turning them into errors. If VALUE hits something it can’t read, such as 450 USD, it returns #VALUE!, which is what you want: the bad row shows up instead of quietly dropping out of the total.

In Excel, cells with a green triangle often offer Convert to Number. I didn’t click that control for this article; the helper column above is the method I tested. The other ways to convert, including Paste Special and Text to Columns, are in converting numbers stored as text.

A criterion that almost matches

Even with E in place, row 6 is still missing. =COUNTIFS(B2:B9,"Greenline",C2:C9,"Paid") returns 3, not 4. =LEN(B6) returns 10 for a 9-letter name, and that extra character is the trailing space.

A quick way to check: add a wildcard. =COUNTIFS(B2:B9,"Greenline*",C2:C9,"Paid") returns 4. When the count jumps like that, there’s something after the name.

Clean the names in a second helper column. In F2, fill down to F9:

=TRIM(CLEAN(SUBSTITUTE(B2,UNICHAR(160)," ")))

SUBSTITUTE turns non-breaking spaces (common in text pasted from web pages) into normal spaces, CLEAN strips control characters, and TRIM removes the leading and trailing spaces. Use UNICHAR(160) rather than CHAR(160): on a Mac, Excel’s CHAR(160) isn’t the non-breaking space. If spaces survive this formula, TRIM not working covers the rarer characters.

Now point the formula at the two helper columns:

=SUMIFS(E2:E9,F2:F9,"Greenline",C2:C9,"Paid") returns 1,545.

With one condition, SUMIF works too, but its argument order is different. The sum range comes last:

=SUMIF(F2:F9,"Greenline",E2:E9) returns 1,545.

If you copy a SUMIFS layout into SUMIF, the sum range and criteria range end up swapped, and you get 0 or a wrong total.

Date criteria written as text

To total Greenline’s payments from October 4 to October 8, the operator goes in quotes and the date is joined on with &:

=SUMIFS(E2:E9,F2:F9,"Greenline",A2:A9,">="&DATE(2026,10,4),A2:A9,"<"&DATE(2026,10,9)) returns 1,095 (790 + 180 + 125).

Putting the whole thing in quotes, as in ">=DATE(2026,10,4)", returns 0. SUMIFS compares the dates with the literal text and nothing matches. I use < the next day instead of <= the last day so that timestamps such as October 8 at 15:30 are still included.

If the formula is right and still returns 0, check the dates. =ISNUMBER(A4) should return TRUE. If it returns FALSE, the dates are text and no date criterion will match them; see dates stored as text.

Two criteria on the same column

=SUMIFS(E2:E9,F2:F9,"Greenline",F2:F9,"Baystone") returns 0. SUMIFS joins its conditions with AND, and no cell is both Greenline and Baystone. For “this customer or that one”, use one of the methods in SUMIFS with OR logic in the same column.

Ranges that don’t line up

This one gives a wrong total, not 0, but it shows up while you’re fixing the others. =SUMIF(F2:F8,"Greenline",E3:E9) returns 880. Both ranges are seven cells long, but the sum range starts one row lower, so every Greenline match picks up the amount from the next row down. Start and end every range on the same rows.

If the ranges are different sizes, SUMIFS returns #VALUE!. SUMIF doesn’t complain. It resizes the sum range to match the criteria range, starting from the sum range’s first cell, so =SUMIF(F2:F9,"Greenline",E2:E3) still returns 1,545. That’s convenient until a misaligned start row gives you a wrong number with no warning.

If Excel still shows the old 0

If the formula and the data already look right and Excel still shows 0, the workbook may be set to manual calculation. I have not re-checked that dialog in Excel for this page. In Excel, look under Formulas > Calculation Options and choose Automatic, or press F9 to recalculate. Google Sheets doesn’t have a manual calculation mode, so this one only applies to Excel.