Excel SUMIFS Not Working? Causes and Fixes

Yes—Excel SUMIFS can return zero, the wrong total, or an error when its ranges, criteria, or data types do not line up. Check that the sum range and every criteria range cover matching rows, then verify that each criterion matches the stored data exactly; those checks resolve many apparent failures.

How SUMIFS works

SUMIFS adds numbers in one range only when corresponding cells meet one or more conditions. Its argument order is important: the sum range comes first, followed by a criteria range and its criterion, with additional range-and-criterion pairs after that. This is the reverse of SUMIF’s argument order, a common source of mistakes when adapting an older formula.

For example, suppose a worksheet has product names in A2:A6, regions in B2:B6, dates in C2:C6, and sales amounts in D2:D6. The five rows are Widget/East/January 5/120, Widget/West/January 10/80, Gadget/East/January 15/200, Widget/East/February 3/150, and Widget/East/January 20/50. With that layout, the following formula totals Widget sales in the East region:

With products in A2:A6, regions in B2:B6, and sales in D2:D6, use:

=SUMIFS(D2:D6,A2:A6,"Widget",B2:B6,"East")

The result is 320: 120 plus 150 plus 50. The West sale and the Gadget sale do not meet both conditions, so they are excluded. SUMIFS applies all supplied criteria together as an AND condition: a row must satisfy every pair to contribute to the total.

Check argument order and range sizes

Read the formula from left to right and label each part: sum range, first criteria range, first criterion, second criteria range, second criterion. If a formula begins with a criteria range where the sum range belongs, Excel may still accept the syntax but sum the wrong cells or return an unexpected result.

All ranges should cover the same number of rows and columns. In the example, D2:D6, A2:A6, and B2:B6 all contain five rows. A range that starts at row 2 while another starts at row 3 is misaligned even if both ranges have the same height: Excel compares cells by position, not by matching labels elsewhere in the sheet. Ranges with different dimensions can produce a #VALUE! error.

Compare the first and last row of every range, not just the column letters.
Make sure each criteria range corresponds to the same records as the sum range.
When copying a formula, check whether references should stay fixed. For example, use absolute references such as $D$2:$D$100 when the range must not shift as the formula is copied.
For tables that grow, consider using Excel table column references so newly added records are included automatically.

Whole-column ranges can be convenient, but they may make a workbook slower when many complex formulas recalculate. Bounded ranges or table references are often easier to audit and keep aligned.

Make sure the criteria match the actual values

SUMIFS criteria can be text, numbers, expressions, or references to cells containing criteria. Exact text criteria generally need to match the cell contents. A trailing space in a source value, a different spelling, or an extra character can make a visually similar label fail to match. A useful check is to inspect the source cells directly, especially if data was pasted or imported from another system.

Criteria can also be built from comparison operators. Put an operator such as greater than or less than inside quotation marks, and join it to a cell reference with the ampersand. For instance, a criterion that means greater than the value in H2 is written as ">"&H2. If H2 contains the number 100, that criterion matches values greater than 100, not values equal to 100.

For text that begins with certain characters, use the wildcard asterisk, which represents any number of characters. The question mark represents one character, and a tilde escapes a wildcard when you need to search for the literal character itself. For example, a criterion of "Pro*" can match text beginning with Pro, such as Product or Promotion. Wildcards are for text matching; they do not correct inconsistent source values.

If criteria are stored in cells, check that the formula points to the intended cells and that copied formulas have not shifted those references. With products in A2:A6, regions in B2:B6, sales in D2:D6, the requested product in H2, and the requested region in H3, use:

=SUMIFS(D2:D6,A2:A6,H2,B2:B6,H3)

If H2 contains Widget and H3 contains East, this returns 320 for the sample data. Cell-based criteria are handy for reports because a user can change a selection without editing the formula.

Diagnose dates and numbers stored as text

A date that looks correct on screen may be text rather than an Excel date value. Formatting changes how a value appears; it does not necessarily convert text into a true date. Similarly, an amount that looks like 120 may be stored as text after an import. SUMIFS cannot reliably treat text values as numeric amounts in the sum range, and text dates may not match date criteria as expected.

Check a suspicious cell by changing its number format or testing it with Excel’s ISNUMBER function. If a date or amount is text, convert the source data to real values before relying on the total. Depending on the data, options include Text to Columns, a suitable conversion formula, or importing the file with the correct data type. Do not convert blindly: confirm that the results represent the intended dates and numbers.

For date periods, use a start date inclusive and the next period’s start date exclusive. This approach includes every time on the final day when the source column contains date-and-time values. With dates in C2:C6, products in A2:A6, regions in B2:B6, and sales in D2:D6, the following adds East-region Widget sales from January 2026:

=SUMIFS(D2:D6,A2:A6,"Widget",B2:B6,"East",C2:C6,">="&DATE(2026,1,1),C2:C6,"<"&DATE(2026,2,1))

For the sample rows, the result is 170: the January sales of 120 and 50. The February 3 sale is excluded. Using a less-than test for February 1 avoids accidentally excluding a January 31 entry that includes a time later than midnight.

Understand zero results and error messages

A result of 0 is not necessarily an Excel error. It often means no row met all the conditions, or the matching rows have no usable numeric values in the sum range. Temporarily simplify the formula by removing one criterion at a time. If the total appears after removing a condition, inspect that condition’s range and values. This isolates the mismatch without changing the source data.

Other signs point to different issues:

#VALUE! can indicate incompatible range dimensions or another invalid reference. Compare the shape and boundaries of every range.
#NAME? can mean Excel does not recognize part of the formula, such as a misspelled function name or text criterion entered without quotation marks.
An unexpectedly small or large total may mean the sum range is pointed at the wrong column, a range stopped before the last record, or copied references shifted.
A formula displayed as text instead of calculating may have been entered into a cell formatted as Text, or may have a leading apostrophe. Change the format to General and re-enter the formula.

Also check whether calculation is set to Manual. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and select Automatic if automatic recalculation is intended. Excel for Mac has calculation options on the Formulas tab as well, though the ribbon layout can differ. If the displayed total looks stale, recalculate after confirming the workbook’s calculation setting.

Check separators, quotes, and Excel compatibility

In many English-language Excel installations, commas separate function arguments. Some regional settings use semicolons instead, so a formula pasted with commas may be rejected. Use the separator expected by that Excel installation; do not mix commas and semicolons within one formula. The formula examples here use commas, as requested for standard Excel syntax.

Text criteria must be enclosed in straight double quotation marks when typed directly into a formula. Curly quotation marks copied from a formatted document are not the same characters and can cause a formula error. A criterion built from a cell reference is different: reference the cell rather than placing the reference inside quotes, or Excel will treat it as literal text.

SUMIFS is available in Excel 2007 and later, including Microsoft 365. If a workbook is opened in a very old spreadsheet program or an older Excel version, compatibility may be the issue. When sharing files, confirm the recipient’s program supports the function and that the workbook is saved in a format that preserves formulas.

Use OR conditions carefully

Multiple SUMIFS criteria are combined with AND, not OR. If the goal is to total sales for either East or West, one approach is to add separate SUMIFS calculations, one for each region. Check that the conditions are mutually exclusive: if a row can satisfy both alternatives, adding the separate results can count it twice. For several alternatives, a helper column or a carefully designed report may be easier to review than a long formula.

For a simple two-condition report, SUMIFS is usually clearer than building a complex array expression. If you only need one condition, SUMIF may be shorter, but remember its different argument order. Choose based on the number of conditions and how easily another person can verify the calculation.

A quick troubleshooting sequence

Use this order to find the cause efficiently, especially in a long formula:

Confirm the function name and argument order, with the sum range first.
Check that every range covers the same records and has the same dimensions.
Test whether each criterion matches the underlying value, including spaces and spelling.
Confirm dates are real dates and amounts are numeric values rather than text.
Remove criteria one at a time to identify which condition stops matching.
Check quotation marks, the regional list separator, calculation settings, and whether the formula is stored as text.

Keep a small known-good example or compare a few source rows manually. A formula can be syntactically correct and still answer a different question from the one intended, so verify both the mechanics and the business meaning of each condition.

Frequently Asked Questions

Why does SUMIFS return 0 when the values look like they match?
Look for extra spaces, text-versus-number differences, dates stored as text, or a criterion referencing the wrong cell. Remove criteria one by one to see which condition prevents a match.

Do all SUMIFS ranges need to be the same size?
Yes. The sum range and each criteria range should have matching dimensions and refer to corresponding rows or columns. Mismatched ranges can cause an error or make the formula compare the wrong records.

Can SUMIFS check a date range?
Yes. Use a lower-bound criterion and an upper-bound criterion on the date range. For date-and-time data, an inclusive start and exclusive next-period start is usually safer than testing for a specific final date.

How do I use OR with SUMIFS?
For separate, non-overlapping choices, add one SUMIFS result for each choice. Check for overlap first, because a record that qualifies for more than one calculation may be counted more than once.

Why does Excel show the formula instead of a total?
The cell may be formatted as Text, the formula may start with an apostrophe, or Show Formulas may be enabled. Check the cell format and formula entry, and toggle Show Formulas from the Formulas tab if the whole worksheet displays formulas.

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.