SUMIFS Not Working? Fix Common Excel Errors

If SUMIFS is returning the wrong total, check that the sum range and every criteria range cover matching cells, then verify that each criterion matches the data exactly. In Excel for Microsoft 365 on Windows, also check date and number types, wildcard use, and calculation mode; these are frequent causes of unexpected results.

How SUMIFS works

SUMIFS adds values that meet all the conditions you specify. Its argument order is sum range first, followed by one or more pairs of criteria range and criterion. A row contributes to the total only when it meets every condition. This is an AND test, not an OR test.

For example, suppose A2:A6 contains product names, B2:B6 contains regions, and C2:C6 contains sales. With those ranges, this formula adds sales for rows where the product is Pen and the region is West. The matching sales are 12 and 8, so the result is 20:

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

Argument order matters. SUMIFS begins with the cells to add; each condition then supplies a range to inspect and a value or rule to match. SUMIF has a different argument order and is intended for one criterion. A formula copied from a SUMIF example may therefore be wrong if its arguments are not rearranged for SUMIFS.

Check range size and argument order

The sum range and criteria ranges must have compatible dimensions. The clearest and safest setup is to use ranges with the same first and last rows, such as C2:C100 for the sum range and A2:A100 and B2:B100 for criteria. A range that starts on a different row can associate a criterion with the wrong sum cell, even if the formula is syntactically valid.

Inspect the formula from left to right and confirm that:

The first argument is the numeric range you want to add.
Each criteria range covers the corresponding records, not a header or a different section of the sheet.
Every criteria range is followed by its intended criterion.
There is no extra or missing comma that shifts an argument into the wrong position.

When copying a formula, relative references can also move. If the criteria are in fixed cells, lock them with dollar signs. With sales in C2:C100, regions in B2:B100, and the chosen region in F1, this formula keeps F1 fixed when copied down:

=SUMIFS($C$2:$C$100,$B$2:$B$100,$F$1)

Excel usually adjusts relative references automatically as a formula is copied. Use dollar signs only where a range or input should remain fixed; locking every reference unnecessarily can make a copied formula harder to adapt.

Make sure the criteria match the data

A criterion can look right to a person and still fail to match. Check for leading or trailing spaces, inconsistent spelling, punctuation, and unexpected characters. For example, a cell containing Pen followed by a space may not behave like a clean Pen entry in every comparison context. TRIM can help clean ordinary spaces in source data, but it does not remove every nonprinting character, such as some nonbreaking spaces copied from a web page.

Text criteria are generally not case-sensitive in Excel, so Pen and PEN normally match the same text. SUMIFS does not, however, treat different words or extra characters as equivalent. If partial matching is intended, use wildcards:

* matches any number of characters. A criterion such as "Pen*" can match Pen Set or Pencils, depending on the text.
? matches one character. A criterion such as "A?" can match A1 or AB, but not a longer string.
Use a tilde before a wildcard character to search for it literally. For example, "~*" matches a literal asterisk.

Wildcards are useful for intentional partial matches, but they can also make a condition too broad. If a result is larger than expected, review whether a wildcard is matching additional categories.

Use operators correctly for numbers and dates

Criteria involving comparison operators need the operator and the target value in the same criterion. When the target comes from a cell, join the operator to the cell reference with an ampersand. With amounts in C2:C20, departments in A2:A20, and a threshold in F1, the formula below adds amounts for Sales records strictly greater than the threshold in F1:

=SUMIFS(C2:C20,A2:A20,"Sales",C2:C20,">"&F1)

For an inclusive comparison, use >= or <=. For a fixed value, place the operator and value together in quotation marks, such as ">100". A common mistake is writing the operator in one argument and the number or cell reference in the next; SUMIFS expects one criterion argument for each criteria range.

Dates cause trouble when a cell that looks like a date is actually stored as text. Excel can compare genuine date values reliably, but text dates may not match date criteria. Check the source cells by changing their number format or testing a known record, and convert text dates to real dates if needed. To sum values for dates in a range, use a start date inclusive and the next day exclusive. With dates in A2:A100, sales in C2:C100, and the start and end dates in F1 and G1, the formula below includes both endpoints:

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

This boundary method also works when the source contains times, because it includes every time on the end date before midnight of the following day. Ensure F1 and G1 contain genuine Excel dates, not text that merely resembles a date.

Look for numbers stored as text

If amounts appear to be numbers but are stored as text, they may not be added as expected. Imported data, pasted values, leading apostrophes, and inconsistent decimal or thousands separators are common sources. A quick visual clue is that numbers stored as text are often left-aligned by default, though formatting can change alignment, so this is not conclusive.

Select a sample cell and inspect the formula bar and number format. You can also test whether Excel recognizes a value as numeric with ISNUMBER. If the data was imported, use the appropriate import settings or convert the column to numbers before relying on a total. Text in a criteria column can similarly differ from a numeric value even when both display the same digits.

Do not solve a data-quality problem by changing the criterion at random. First decide whether the source should represent a number, a date, or text, then standardize the source column. Consistent source data makes the SUMIFS result easier to audit and prevents later formulas from failing for the same reason.

Understand multiple conditions and OR logic

Each additional criteria pair in one SUMIFS formula narrows the matches: a row has to satisfy all the criteria. For instance, a formula with a department condition and a month condition returns rows in that department and in that month. If you expected either condition to qualify a row, a single SUMIFS formula will not express that OR test.

For a small, known list of alternatives, add separate SUMIFS calculations. With product names in A2:A20 and sales in C2:C20, this formula adds sales for either Pen or Pencil. If the Pen sales are 30 and Pencil sales are 18, the result is 48:

=SUMIFS(C2:C20,A2:A20,"Pen")+SUMIFS(C2:C20,A2:A20,"Pencil")

Be careful that the alternatives do not overlap. If a record can match more than one condition in the separate calculations, it may be counted twice. For a different kind of OR logic, such as values above or below a threshold, write separate calculations only after confirming the categories are mutually exclusive and the intended total is clear.

Check calculation mode and workbook behavior

A correct formula can appear stale if the workbook is set to manual calculation. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and select Automatic. Then press F9 to recalculate the workbook. If Excel shows the formula text in the cell instead of a result, check whether the cell is formatted as Text or whether Show Formulas is enabled. Change the cell to General, re-enter the formula, and toggle Show Formulas from the Formulas tab if needed.

Also check for an apostrophe before the equals sign, which makes an entry text rather than a formula. If a result shows ####, that usually indicates the column is too narrow to display the value; it does not by itself mean SUMIFS calculated incorrectly. A result of zero can mean no rows matched, or it can mean the matched sum cells contain zeros or text rather than numbers.

For a quick diagnostic, use Formulas > Evaluate Formula to step through a formula’s calculation. You can also filter the source table to the conditions used in the formula and manually compare the visible matching rows and amounts. Filtering is a useful check, but remember that SUMIFS normally includes qualifying rows hidden by a filter; hiding a row does not exclude it from the calculation.

SUMIFS versus SUMIF and other approaches

Use SUMIFS when the total depends on multiple conditions, or when you want the consistent sum-range-first argument order. Use SUMIF for a single condition if that makes the formula clearer. SUMIF can also handle a single criterion, but its argument order is criteria range, criterion, then optional sum range, unlike SUMIFS.

For totals that need to respond to many different categories, a PivotTable may be easier to inspect than a long formula. For a compact report with a few fixed conditions, SUMIFS is often simpler and updates when source values change. Structured references in an Excel Table can also make formulas easier to maintain because the references use column names and expand as table rows are added. Whichever approach you choose, verify that it includes the same records and uses the intended treatment of filters and blanks.

A practical troubleshooting order

Work through the likely causes in a consistent order rather than rewriting the entire formula at once:

Confirm the first argument is the range to sum and the remaining arguments are range-and-criterion pairs.
Compare the first and last rows of all referenced ranges.
Check spelling, spaces, wildcard characters, and the exact value in each criterion cell.
Verify that the compared dates and numbers are stored as real dates and numbers, not text.
Decide whether the conditions are meant to work together (AND) or as alternatives (OR).
Check calculation mode, formula-as-text symptoms, and whether the expected records are actually present.

Keeping the source data in a clean Excel Table, with one type of information per column and a single header row, reduces many recurring SUMIFS problems. It also makes it easier to filter and audit the records behind a total.

Frequently Asked Questions

Why does SUMIFS return 0 when matching rows are visible?
Check for text-versus-number mismatches, extra spaces, dates stored as text, and criteria that do not exactly match the source. Also confirm that the sum cells contain numeric values and that the criteria ranges line up with the sum range.

Does SUMIFS include filtered or hidden rows?
Yes. SUMIFS evaluates qualifying cells even if their rows are hidden or excluded by a filter. If you need a total that changes according to visible rows, ordinary SUMIFS is not designed to do that by itself.

Can SUMIFS use more than one condition?
Yes. Add a criteria-range and criterion pair for each additional condition. The row must meet all the conditions in that one formula.

How do I make SUMIFS match either one value or another?
For a short list of alternatives, add separate SUMIFS formulas, one for each value. Check for overlapping matches so the same record is not counted more than once.

Why does SUMIFS not recognize my date?
The displayed date may be text rather than an Excel date value. Convert the source and boundary cells to genuine dates, then use comparison criteria such as greater than or equal to the start date and less than the day after the end date.

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.