SUMIFS Function Not Working? Fix Common Excel Errors

If SUMIFS is returning zero or the wrong total, check that its sum range and criteria ranges cover the same rows, then confirm that each criterion matches the underlying data exactly. For example, with product names in A2:A6, regions in B2:B6, and sales in C2:C6, =SUMIFS(C2:C6,A2:A6,"Pens",B2:B6,"West") returns 245 when the matching West-region pen sales are 120, 75, and 50.

Start with the formula structure

SUMIFS adds values that meet one or more conditions. In Excel for Microsoft 365 on Windows, its argument order is the sum range first, followed by a criteria range and its criterion, repeated for each additional condition. A misplaced argument can make a formula return an unexpected result without producing an error.

For a simple check, read the formula from left to right: identify what should be added, then identify each column Excel should test and the condition it should apply. In the example above, Excel adds sales from column C only where column A is Pens and column B is West. The two conditions are combined with AND logic: a row must meet both.

Confirm the first argument contains the numbers you intend to add.
Pair every criteria range with the criterion meant for that range.
Check that the criteria are joined with commas and that parentheses are balanced.
Compare a few matching rows by eye to see whether the expected total is plausible.

If the result is zero, that often means Excel found no rows meeting all the conditions—not necessarily that the function itself is broken. If it returns a number that is too large or small, a range may include extra rows, omit data, or contain values that are not stored as numbers.

Check range sizes and row alignment

The sum range and all criteria ranges should cover corresponding rows. A frequent mistake is using sales from C2:C100 but checking a criterion range that begins at row 3, such as A3:A100. Even if the ranges look similar, their values are no longer aligned row by row. Excel can also return a #VALUE! error when the ranges have incompatible dimensions.

With product names in A2:A100, regions in B2:B100, sales in C2:C100, and the desired product and region in E2 and F2, respectively, this formula keeps every data range aligned:

=SUMIFS(C2:C100,A2:A100,E2,B2:B100,F2)

Make sure the formula’s last row includes the newest records, but avoid extending only one range. Whole-column references can be convenient, but for a large or complicated workbook, consistent bounded ranges or an Excel Table can make the setup easier to maintain. To create a table, select the data and choose Insert > Table. When rows are added to a table, its structured references generally expand with the data.

Also inspect blank rows, totals, and duplicate records. If a total row is inside the range, its value might be included in the result alongside the detail rows. Conversely, records below the range will not be counted. Filtering a worksheet does not make SUMIFS ignore hidden rows: the function includes qualifying rows whether or not a filter currently displays them.

Make sure criteria match the stored values

What looks identical on screen may not be identical to Excel. A cell might contain a trailing space, a nonbreaking space copied from a web page, or a slightly different spelling. For instance, Pens and Pens followed by a space can appear the same in a narrow column but behave as different text. SUMIFS text matching is not case-sensitive, so capitalization alone is usually not the cause.

Use a helper column or inspect a suspicious cell in the formula bar. TRIM can remove ordinary leading and trailing spaces, though it does not remove every special whitespace character. For copied data, replacing the unwanted character or cleaning the source may be more reliable than changing the SUMIFS formula. If a criterion comes from a cell, make sure that cell contains the expected value rather than a label, blank, or formula result with extra characters.

Number values stored as text are another common cause. A cell showing 100 may contain the text string 100 rather than the number 100. SUMIFS can then fail to sum it as intended, or a numeric criterion may not match the text-formatted value. Check alignment and number formats, but remember that changing a cell’s number format alone does not convert text into a number. Use Excel’s available conversion options, or correct the source data, then verify the results.

Criteria can also include comparison operators. With status values in A2:A100, amounts in B2:B100, and the goal of adding amounts for every status except Closed, use:

=SUMIFS(B2:B100,A2:A100,"<>Closed")

For criteria that include an operator and a cell reference, join the operator text to the cell reference with an ampersand. With amounts in B2:B100, dates in A2:A100, and a cutoff date in E2, this adds amounts dated on or after the cutoff:

=SUMIFS(B2:B100,A2:A100,">="&E2)

Ensure E2 contains a real Excel date, not text that merely looks like one. A date entered as text may not compare as expected with Excel’s numeric date values.

Troubleshoot dates and date ranges

Date criteria often fail because a cell contains text rather than a date, or because the data includes times as well as dates. A value formatted to display as 1-Nov can still include a time such as 10:30 a.m. A criterion for exactly 1-Nov may not include that value if the formula is testing for equality with midnight.

For a daily total that includes every time on a selected date, use a start-inclusive and next-day-exclusive test. With transaction dates and times in A2:A100, amounts in B2:B100, and the target date in E2, the formula is:

=SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&E2+1)

This includes timestamps from the start of E2 up to, but not including, the next day. For a month, use the first day of the month as the start and the first day of the following month as the exclusive end. Check that E2 is a genuine date value and that the date range contains the same kind of values. Formatting changes how dates look; it does not repair text dates or remove hidden time values.

Use wildcards carefully

For text criteria, an asterisk represents any sequence of characters, while a question mark represents one character. For example, a criterion of North* can match North, Northside, or North Region. A wildcard is useful when entries have predictable variations, but it can also match more rows than intended. If you need an exact text match, use the full criterion without a wildcard.

With descriptions in A2:A100 and amounts in B2:B100, this formula adds values for descriptions that begin with North:

=SUMIFS(B2:B100,A2:A100,"North*")

If the text you need to match contains a literal asterisk or question mark, precede that character with a tilde in the criterion. A tilde can also be escaped by using two tildes. Before relying on a wildcard total, filter or inspect the matching text entries to confirm the pattern captures only the intended records.

Find whether the problem is the data or the formula

Break a multi-condition formula into smaller checks. First test whether each criterion finds any rows; then compare those counts with the rows you expect to qualify. A count of zero points toward a mismatched criterion, a range that misses the records, or inconsistent data. A positive count with a zero sum points more strongly toward the sum range or values stored as text.

With product names in A2:A100, regions in B2:B100, and the criteria in E2 and F2, respectively, this check counts rows meeting both conditions:

=COUNTIFS(A2:A100,E2,B2:B100,F2)

COUNTIFS does not prove the sum is correct, but it can show whether Excel recognizes any matching records. For a particularly confusing result, temporarily remove one criteria pair at a time and compare the totals. If the single-condition total is right but adding the next condition makes it zero, focus on that second criteria range and criterion. Restore the full formula after troubleshooting so the final calculation still applies every required condition.

Also check whether the displayed result is stale. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic if the workbook is set to manual calculation. Press F9 to calculate open workbooks. If other formulas update but SUMIFS does not, inspect the formula and its source data rather than assuming calculation mode is the cause. In Excel for Mac, calculation controls are also available from the Formulas tab; ribbon layout can vary by release.

SUMIFS versus SUMIF

SUMIF handles one condition, while SUMIFS handles multiple conditions. Their argument orders differ, which can cause a formula copied or rewritten from one function to be wrong. SUMIF places its criteria range and criterion first, then the optional sum range. SUMIFS places the sum range first, then its criteria-range-and-criterion pairs. When translating a formula, check the argument order rather than simply adding an S to the function name.

For a single region condition, with regions in B2:B100 and amounts in C2:C100, use:

=SUMIF(B2:B100,"West",C2:C100)

For more than one condition, use SUMIFS and add a pair for each additional test. It is also important to distinguish a formula issue from a requirements issue: SUMIFS applies AND logic across its criteria pairs. If the intended rule is “West or East,” two separate SUMIFS results may need to be added, or the data may need a different approach. Do not add criteria pairs expecting them to behave as alternatives.

Frequently Asked Questions

Why does SUMIFS return 0 when matching rows exist?

Check for text-versus-number differences, extra spaces, incorrect criteria references, and ranges that begin or end on different rows. Confirm that every condition is true on the same row; matching the product on one row and the region on another does not satisfy both conditions.

Does SUMIFS ignore filtered or hidden rows?

No. SUMIFS normally includes all qualifying rows in its ranges, including rows hidden by a filter or manually hidden. If you need a total that changes according to which rows are visible, SUMIFS alone is not designed to provide that behavior.

Is SUMIFS case-sensitive?

No. Standard SUMIFS text criteria do not distinguish uppercase from lowercase. If two labels differ only by capitalization, they generally match the same criterion; check spelling, spaces, and other characters instead.

Why does SUMIFS show #VALUE!?

Check that the sum range and each criteria range have compatible dimensions and are properly referenced. Also inspect any referenced cells or source formulas for errors, since errors in the data can affect the result.

Can SUMIFS add values for either of two criteria?

Criteria pairs in one SUMIFS formula are combined with AND, not OR. For two alternatives on the same field, add the results of separate SUMIFS calculations or restructure the calculation so it explicitly represents the OR condition.

A reliable final check

When a SUMIFS result still looks wrong, verify the formula against the worksheet one condition at a time: confirm the sum range, confirm each paired criteria range, and inspect the actual stored values in a few rows that should match. Then check for dates stored as text, numbers stored as text, wildcard overmatching, omitted records, and calculation settings. These checks usually reveal whether the issue is formula structure, source data, or simply a different rule than the formula currently expresses.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.