Skip to content
ArumSoft Learning
KO
    SUMIFS = 0Excel

    Why SUMIFS returns 0 (when there are matches)

    Works in: Excel 2010 and later, including Microsoft 365, Excel for Mac and Excel for the web · Updated

    Quick answerUsually a criterion doesn't match the data exactly: dates or amounts stored as text, or a trailing space in the criteria column. For a date criterion, join the operator and the date with &: =SUMIFS(C2:C100,A2:A100,">="&DATE(2026,1,1)).

    Causes and fixes

    1. The date criterion is written as one text string
    2. The dates in the data are stored as text
    3. The amounts to add up are stored as text
    4. Extra spaces in the criteria column
    5. The arguments are in SUMIF order
    6. No row meets every condition at once

    1The date criterion is written as one text string

    Inside quotes, everything is text. ">=F1" doesn't mean "greater than or equal to the date in F1": it compares the dates with the letters F and 1, so nothing matches.

    How to recognise it

    The criterion contains a cell reference or a function inside the quotes, such as ">=F1" or "<TODAY()".

    Fix

    Keep only the operator in quotes and join the cell or function with &:

    =SUMIFS(C2:C100,A2:A100,">="&F1,A2:A100,"<="&G1)

    For a fixed date, use DATE so it doesn't depend on your regional date settings: ">="&DATE(2026,1,1).

    2The dates in the data are stored as text

    Dates pasted from a website or an export often arrive as text. A text "date" is never greater than or less than a real date, so a date range finds nothing.

    How to recognise it

    • The dates line up on the left of the cell.
    • =ISTEXT(A2) returns TRUE.
    • Changing the number format doesn't change how they look.

    Fix

    Select the date column and use Data > Text to Columns, click Next twice, choose Date with the order the text uses (for example MDY) and click Finish. More ways are in Excel dates stored as text.

    3The amounts to add up are stored as text

    SUMIFS only adds real numbers. If the sum range holds numbers stored as text, the matching rows contribute nothing, and the result is 0.

    How to recognise it

    =COUNT(C2:C100) returns 0 or fewer than the number of amounts, and the cells show a green triangle.

    Fix

    Select the amounts, click the warning icon and choose Convert to Number. See Why Excel SUM shows 0 for other ways.

    4Extra spaces in the criteria column

    "North " with a space at the end doesn't match "North". Exported data often has trailing spaces or non-breaking spaces you can't see.

    How to recognise it

    Compare =LEN(B2) with the number of characters you can see. Or test with a wildcard: if =COUNTIFS(B2:B100,"*North*") finds rows that =COUNTIFS(B2:B100,"North") doesn't, there's something around the text.

    Fix

    Clean the column in a helper column with =TRIM(SUBSTITUTE(B2,CHAR(160)," ")), then paste the results back as values. A wildcard criterion such as "*North*" also works, but it matches "Northeast" too.

    5The arguments are in SUMIF order

    SUMIF and SUMIFS take their arguments in a different order. SUMIF is =SUMIF(range, criteria, sum_range); SUMIFS starts with the range to add: =SUMIFS(sum_range, criteria_range, criteria, …).

    How to recognise it

    The first range in your SUMIFS is the column you're testing, not the column of amounts. Excel either refuses the formula or adds up the wrong column.

    Fix

    Put the amounts first:

    =SUMIFS(C2:C100,B2:B100,"North")

    6No row meets every condition at once

    SUMIFS adds a row only when all the conditions are true for that row. Two conditions that each match some rows can still have no rows in common.

    How to recognise it

    Test each condition on its own with COUNTIFS. If each finds rows but both together find 0, the data has no overlap.

    =COUNTIFS(B2:B100,"North")
    =COUNTIFS(A2:A100,">="&F1)
    =COUNTIFS(B2:B100,"North",A2:A100,">="&F1)

    Fix

    Check the condition that drops to 0, often a date range that ends a day too early. For "either/or" conditions, add two SUMIFS together instead of putting both in one.

    Still stuck?

    • All the ranges must be the same size, such as A2:A100 and C2:C100. Different sizes return #VALUE!, not 0.
    • SUMIFS isn't case-sensitive, so "north" and "North" match.
    • SUMIFS adds hidden and filtered rows too. Use SUBTOTAL or AGGREGATE to add only visible rows.
    • Check Formulas > Calculation Options is set to Automatic.