Why Are My SUMIFS Not Working? Excel Fixes

When SUMIFS is not working in Excel, the most common causes are mismatched range sizes, criteria that do not match the underlying data, or a criterion written with the wrong syntax. Check the function’s argument order first, then test whether any rows meet the criteria before investigating dates, spaces, or calculation settings.

How SUMIFS works in Excel

SUMIFS adds values from a sum range only when the corresponding rows meet every condition you specify. Its argument order is important: the range to add comes first, followed by a criteria range and its criterion, repeated in pairs for additional conditions. This differs from SUMIF, which puts its criteria range first.

For example, imagine a small table with regions in A2:A5, products in B2:B5, and sales in C2:C5. The rows are North, Tea, 40; North, Coffee, 60; South, Tea, 25; and South, Coffee, 35. With North in E2 and Tea in F2, this formula returns 40:

With regions in A2:A5, products in B2:B5, sales in C2:C5, and the criteria North in E2 and Tea in F2, use =SUMIFS(C2:C5,A2:A5,E2,B2:B5,F2).

The formula requires both conditions to be true for a row to contribute to the total. If either condition is wrong or matches no rows, the result may be zero even though the formula itself is valid. SUMIFS is not case-sensitive for ordinary text criteria, so North and NORTH are treated as equivalent.

Check range size and alignment

Every criteria range should have the same number of rows and columns as the sum range. If you sum C2:C100 but use A2:A99 as a criteria range, the inconsistent dimensions can produce an error. Even when ranges have matching dimensions, a starting-row mismatch can pair the wrong records: summing C2:C100 while checking A3:A101 compares each amount with the next row’s criterion.

Compare the range references directly in the formula bar. Look for an accidental missing row, an extra header row, or references that shifted when the formula was copied. A dollar sign locks a reference when you fill a formula, but it does not fix a range that was defined incorrectly in the first place. For instance, $C$2:$C$100 stays fixed when copied, whereas C2:C100 may move.

Whole-column references such as C:C and A:A have matching dimensions and are often convenient. For a large or complex workbook, however, bounded ranges or an Excel Table can be easier to inspect and may reduce unnecessary calculation work. Whichever style you use, make sure each criteria range covers the same records as the sum range.

Verify the criteria syntax

For a text match, a cell reference is usually the clearest criterion. For comparisons, put the operator inside quotation marks and join it to the cell containing the comparison value. A common mistake is typing a comparison as literal text, so Excel looks for the characters rather than applying the comparison.

With amounts in B2:B100, departments in A2:A100, the department criterion in E2, and a minimum amount in F2, this formula sums amounts for that department that exceed the value in F2:

=SUMIFS(B2:B100,A2:A100,E2,B2:B100,