SUMIFS Won’t Work: Causes and Fixes

SUMIFS usually fails because its ranges do not line up, its criteria do not match the values in the data, or the criteria are written with the wrong syntax. Check those issues first; then confirm that the formula points to the intended cells and that Excel has recalculated it.

SUMIFS is designed to add numbers only when every specified condition is met. That makes it useful for reports, but a small difference between what a criterion says and what the worksheet actually contains can produce a zero or an incomplete total without an obvious error.

How SUMIFS works

In Microsoft Excel for Microsoft 365 on Windows, SUMIFS uses this argument order: the cells to add first, then a criteria range and its condition, followed by any additional range-condition pairs. The sum range and every criteria range need to have matching dimensions. The formula returns the sum of values in rows where all the criteria are true.

For example, with item names in A2:A6, regions in B2:B6, and sales amounts in C2:C6, SUMIFS can total sales for one item in one region. If the matching rows contain 120 and 80, the answer is 200. Both conditions must be true on the same row; a row with the right item but the wrong region is excluded.

With item names in A2:A6, regions in B2:B6, and sales amounts in C2:C6, this formula adds sales for apples in the West:

=SUMIFS(C2:C6,A2:A6,"Apples",B2:B6,"West")

The first argument is the sum range, C2:C6. Each following pair says where to look and what to match. A frequent source of trouble is assuming that SUMIFS takes its arguments in the same order as SUMIF: it does not. SUMIF puts its criteria range first, while SUMIFS puts its sum range first.

Check the ranges and argument order

Review the formula from left to right and verify each range against the worksheet. A sum range covering rows 2 through 100 should normally be paired with criteria ranges covering those same rows. A criteria range that starts at row 1 or ends at row 99 is a warning sign: Excel needs corresponding cells to represent the same records.

Range-size mismatches can cause an error or results that do not reflect the rows you intended. Also check that each condition is paired with the correct range. If the formula is meant to filter by region and product, accidentally pairing the product criterion with the region column may return zero even though both values exist somewhere in the data.

When a formula is copied down or across, relative references can shift. Lock a range with dollar signs when it must stay fixed. For example, with item names in A2:A100, regions in B2:B100, amounts in C2:C100, and a region to select in E1, this formula keeps the data ranges fixed when copied:

=SUMIFS($C$2:$C$100,$A$2:$A$100,"Apples",$B$2:$B$100,E$1)

The dollar signs do not change the calculation by themselves; they prevent references from moving during copying. Use the reference style that fits your layout, and make sure each criteria range still aligns with the sum range.

Make criteria match the actual data

A criterion must match the stored value, not merely what a cell appears to show. Text can contain leading or trailing spaces, different spellings, or nonbreaking spaces copied from a website. “West” and “West ” look nearly identical but are different text values. Inspect a suspect cell in the formula bar, or compare it with a known-good value by copying and pasting that value into the criterion cell.

Text criteria are generally not case-sensitive in SUMIFS, so “west” can match “West.” However, punctuation, extra characters, and spaces can still matter. If data was imported, use Excel’s Find and Replace or clean a copy of the source data before relying on it. TRIM can remove ordinary leading, trailing, and repeated spaces, but it does not remove every special whitespace character.

Numbers stored as text are another common mismatch. A cell may display 125 but contain text rather than the number 125. This can affect both criteria and values to be added. Check alignment and use a simple test such as ISNUMBER on a sample cell if needed. Convert imported values to numbers before summing; changing the number format alone changes appearance, not the underlying value.

Dates also need attention. Excel stores real dates as serial numbers and formats them for display. Text that looks like a date may not compare as a real date, and ambiguous date text can be interpreted differently by regional settings. Confirm that the source column contains actual dates, not imported strings that only resemble dates.

Write operators, dates, and wildcards correctly

Criteria such as greater than, less than, or not equal to combine an operator with a value. When the value comes from a cell, join the operator in quotation marks to the cell reference with an ampersand. Putting the operator and cell address together inside one quoted string makes Excel search for those literal characters instead of using the cell’s value.

With dates in A2:A10 and amounts in B2:B10, and the cutoff date stored as a real Excel date in D1, this formula totals amounts later than the cutoff:

=SUMIFS(B2:B10,A2:A10,">"&D1)

For a date period, use a lower bound and an upper bound. If the dates include times, use a strict upper bound before the next day rather than “less than or equal to” the final date; a timestamp later that day is greater than midnight. With dates in A2:A100, amounts in B2:B100, and the first day of the month in D1, this formula includes all times on dates in that month:

=SUMIFS(B2:B100,A2:A100,">="&D1,A2:A100,"<"&EDATE(D1,1))

Wildcards are available for text matching: an asterisk represents any number of characters, and a question mark represents one character. A tilde escapes a wildcard when you want to match it literally. For instance, a criterion ending in an asterisk can match product names that begin with a chosen prefix. Remember that a wildcard condition is not the same as an exact match, so use it only when partial matching is intended.

Understand zero results and errors

A result of 0 does not necessarily mean SUMIFS is broken. It often means no row satisfied every condition, or that matching rows contain no numeric values in the sum range. Test each condition separately by temporarily simplifying the formula or filtering the source table. If one condition alone returns a plausible total but adding another makes it zero, inspect that second criterion and its range.

A quick way to isolate the issue is to count matching rows with COUNTIFS, which uses criteria ranges and conditions but does not add a sum range. With item names in A2:A100 and regions in B2:B100, this counts rows for apples in the West:

=COUNTIFS(A2:A100,"Apples",B2:B100,"West")

If the count is zero, focus on the criteria, data types, spaces, and ranges. If the count is positive but SUMIFS returns zero, check whether the corresponding sum cells are numeric and whether they contain nonzero values.

An error such as #VALUE! calls for checking range dimensions and any errors already present in referenced cells. A formula displayed as text rather than calculated may mean the cell is formatted as Text, or that an apostrophe precedes the equals sign. Change the format to General, then re-enter the formula. Also confirm that Show Formulas is not enabled if the worksheet is displaying formulas throughout instead of their results.

Check calculation and regional settings

If the formula is correct but its result seems stale, check Excel’s calculation mode. On Windows, use Formulas > Calculation Options and select Automatic if that is appropriate for the workbook. A workbook in Manual calculation mode may not update results until recalculated. Press F9 to calculate; Ctrl+Alt+F9 forces a full recalculation. On Mac, the Calculation Options command is also on the Formulas tab, though keyboard shortcuts can differ by keyboard and Excel version.

In some regional configurations, Excel expects semicolons rather than commas between function arguments. If Excel rejects a formula copied from a comma-using example, your list separator may differ. Use the separator Excel accepts in your installation; do not change the logic or remove required argument pairs. Formula names and separators can also vary with language settings.

In Microsoft 365, the function name is SUMIFS in English-language Excel. If a formula was copied from another spreadsheet program or a localized version of Excel, verify that the function name and separators are recognized by your copy of Excel.

When multiple conditions mean OR

SUMIFS applies AND logic across its criteria pairs: every condition must be satisfied by a row. If the goal is to add values for either of two regions, placing both regions as separate criteria pairs would require one row to be in both regions at once. That usually returns zero because a row cannot have two different values in the same region cell.

For a short list of alternatives, add separate SUMIFS calculations. With regions in A2:A20 and amounts in B2:B20, this totals rows from either East or West:

=SUMIFS(B2:B20,A2:A20,"East")+SUMIFS(B2:B20,A2:A20,"West")

For larger lists, a helper column, a PivotTable, or a formula designed around the actual data structure may be easier to maintain. Be alert to overlap: if alternatives can both match the same record, simply adding results may count that record twice.

A practical troubleshooting sequence

Work from the simplest checks to the less obvious ones rather than rewriting the formula at random. This sequence often reveals the mismatch quickly:

Confirm the function is written as SUMIFS and that the sum range is the first argument.
Check that the sum range and every criteria range cover the same rows and columns.
Verify that each criteria range is paired with the right condition and that all intended conditions should apply together.
Test criteria individually, then inspect text, spaces, numbers stored as text, and date values in the source.
Check operators, quotation marks, wildcards, and cell references used to build criteria.
Look for errors, text-formatted formulas, calculation mode, and regional argument separators.

For a repeatable report, consider converting the data range into an Excel table with Insert > Table. Structured references can expand as new rows are added, reducing the risk that a fixed range omits recent records. Tables do not correct mismatched data or criteria, however; accurate values and sound logic are still necessary.

Frequently Asked Questions

Why does SUMIFS return 0 when the values are visible?
Visible values may not equal the underlying values. Check for extra spaces, text instead of numbers, dates stored as text, a mismatched range, or an additional condition that excludes the row. Test criteria one at a time and confirm the corresponding sum cells are numeric.

Does SUMIFS use AND or OR logic?
SUMIFS uses AND logic between criteria pairs: a row must meet every condition to be included. To total rows matching either of two conditions, use separate SUMIFS calculations and add them, taking care not to double-count rows when conditions overlap.

Why does my SUMIFS formula show instead of calculating?
Check whether the cell is formatted as Text or the formula begins with an apostrophe. Change the cell format to General and enter the formula again. If formulas appear across the worksheet, turn off Show Formulas from the Formulas tab.

Can SUMIFS use a date range?
Yes. Use a start-date condition and an exclusive condition for the day after the end date. This approach also includes timestamps on the final date. Ensure the date cells and the boundary dates are real Excel dates rather than text.

What is the difference between SUMIF and SUMIFS?
SUMIF is for one condition and places its criteria range before its optional sum range. SUMIFS supports multiple conditions and places the sum range first. That argument-order difference is a common reason a formula fails when converted from one function to the other.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.