SUMIFS Excel: Common Errors and How to Fix Them

SUMIFS errors usually come from a misplaced argument, ranges that do not match, or criteria that do not match the data exactly. Check that the sum range comes first, every criteria range has the same shape, and each criterion uses the right text, number, date, or wildcard; if the formula is valid but returns 0, investigate the criteria rather than looking for an error code.

How SUMIFS works

SUMIFS adds numbers from one range only when every condition you specify is met. Its first argument is the range to add; the remaining arguments come in pairs: a range to check, then the condition to apply to that range. You can add several condition pairs, such as one for a region and another for a date period.

For a consistent example, assume a worksheet has dates in A2:A10, regions in B2:B10, and sales in C2:C10. The rows contain East sales of 120 and 80 in January, 150 and 70 in February, and 60 and 100 in March; West sales are 90 in January, 110 in February, and 130 in March. With those columns, a formula for East sales in January is =SUMIFS(C2:C10,B2:B10,"East",A2:A10,">="&DATE(2026,1,1),A2:A10,"<"&DATE(2026,2,1)). It returns 200: the two qualifying East sales are 120 and 80.

The formula’s range-and-condition pairs can appear in either order after the sum range, but each range must stay next to its own criterion. All three ranges in this example cover rows 2 through 10. That alignment is important both for correct results and for avoiding errors.

Argument order and range-size mistakes

A frequent mistake is putting the criteria range first, as if SUMIFS were the older SUMIF function. SUMIFS begins with the sum range. SUMIF instead takes the criteria range first, followed by its condition and, optionally, the sum range. Mixing up these signatures can produce a plausible-looking formula that sums the wrong cells or gives an unexpected result.

With regions in B2:B10 and sales in C2:C10, the SUMIFS formula for East sales is =SUMIFS(C2:C10,B2:B10,"East"). It returns 580: 120 + 80 + 150 + 70 + 60 + 100. The comparable SUMIF formula with the same layout is =SUMIF(B2:B10,"East",C2:C10). It also returns 580, but the argument order is different.

Check every range’s starting and ending row. For the example, B2:B10 is aligned with C2:C10, whereas B2:B9 is not. SUMIFS expects the sum range and each criteria range to have the same number of rows and columns. Unequal dimensions can cause a #VALUE! error or an incorrect result, depending on the ranges and formula. Select the ranges together or use matching table columns to reduce the chance of a mismatch.

Why a valid formula returns zero

SUMIFS normally returns 0 when it finds no rows meeting all the conditions. That is not necessarily a formula error: it may mean there are no qualifying records, or that a condition differs from the stored data. Check for trailing spaces, inconsistent spelling, a criterion applied to the wrong column, or a number stored as text.

For example, if B2:B10 contains the region names and D1 contains East, the formula for East sales is =SUMIFS(C2:C10,B2:B10,D1). With D1 set to East, it returns 580. If D1 contains East followed by a space, it may not match cells that contain only East. To reveal spaces, inspect a suspicious cell in the formula bar, or compare its length with a known-good entry using LEN.

Numbers stored as text are another common source of a zero result. A cell may look like 120 but contain text rather than a numeric value, particularly after importing data. SUMIFS can sum numeric values in its sum range, but text values there are not added as numbers. Likewise, a criterion of the number 120 may not match a text entry that looks like 120. Check the cell format and the underlying value; converting imported values to numbers may be necessary. Changing a cell’s number format alone does not convert text into a number.

Criteria syntax: operators, text, and wildcards

For an exact text match, put the text criterion in straight double quotes, or refer to a cell containing the criterion. For comparisons, place the operator and value together as a criterion. When the value is in another cell, join the operator to that cell with the ampersand. This distinction matters because a criterion such as greater than or equal to a value must be passed as one condition, not as separate arguments.

With sales in C2:C10 and regions in B2:B10, the formula for East sales greater than 100 is =SUMIFS(C2:C10,B2:B10,"East",C2:C10,">100"). It returns 370: the qualifying East values are 120, 150, and 100 is excluded because the condition is strictly greater than 100. If the condition should include 100, use the greater-than-or-equal operator instead.

With regions in B2:B10, sales in C2:C10, and the cutoff in D1 set to 100, the formula for East sales at least as large as that cutoff is =SUMIFS(C2:C10,B2:B10,"East",C2:C10,">="&D1). It returns 370: 120, 150, and 100 meet the condition. If you type a comparison criterion directly, keep the operator inside the quoted text.

Wildcards can help match text patterns. An asterisk matches any number of characters, a question mark matches one character, and a tilde escapes a wildcard when you need to search for a literal asterisk or question mark. With product descriptions in A2:A10 and amounts in B2:B10, the formula for amounts where the description begins with Apple is =SUMIFS(B2:B10,A2:A10,"Apple*"). Its result depends on the actual descriptions and amounts in those cells. Wildcards apply to text matching, not numeric comparisons.

Date criteria and times hidden in cells

Excel stores valid dates as serial numbers, even though the cells display calendar dates. A criterion may fail when a date is stored as text, when a typed date is interpreted differently under regional settings, or when the cells include times that are not visible in the date format. For a reliable period test, use a start date inclusive and the next period’s start exclusive. That approach includes every time on the final day, not just entries recorded at midnight.

With dates in A2:A10, regions in B2:B10, and sales in C2:C10, the formula for all East sales in February 2026 is =SUMIFS(C2:C10,B2:B10,"East",A2:A10,">="&DATE(2026,2,1),A2:A10,"<"&DATE(2026,3,1)). It returns 220: the February East entries are 150 and 70. DATE constructs a date value and avoids ambiguity between regional date formats.

If the result is zero, confirm that the dates are genuine Excel dates rather than text that only looks like a date. Also check whether the source dates include times. A test for equality with a date can miss a value such as noon on that date, since the stored value includes a time fraction. The inclusive-start, exclusive-end pattern handles such entries within the period.

Understanding common error messages

Not every wrong SUMIFS result displays an Excel error. A zero is often a valid result for no matches; an incorrect nonzero total can mean the formula is summing a misaligned range or applying a condition to the wrong field. Error codes offer more specific clues:

#VALUE! can indicate criteria ranges and the sum range have incompatible dimensions. Check that they cover the same rows and columns. Some formulas that refer to an external workbook can also return #VALUE! when that workbook is closed; if the calculation depends on an external source, open it and check whether the result changes.
#REF! means a formula contains an invalid cell reference, often because a referenced row, column, or worksheet was deleted. Edit the formula to point to the intended range again.
#NAME? often means Excel does not recognize part of the formula. Check for a misspelled function name, a missing pair of quotes around typed text, or a named range that does not exist.

Use Formulas > Evaluate Formula to step through a complex formula on Windows and inspect how Excel evaluates its parts. On Mac, use the Formula Evaluation tool if it is available in your Excel version; ribbon availability and layout can differ. The formula bar is also useful for checking whether a criterion has an extra space or an unexpected reference.

References that change when a formula is copied

When you copy a formula down a worksheet, relative references move. That is helpful when each row should use a different criterion, but troublesome when every copied formula should use the same criteria cell or fixed data range. Use dollar signs to make a reference absolute, or lock only its column or row when that is what the layout requires.

With regions in B2:B10, sales in C2:C10, and a fixed criterion in F1, the formula for East sales that can be copied without shifting the data ranges or criterion is =SUMIFS($C$2:$C$10,$B$2:$B$10,$F$1). If F1 contains East, the result is 580. On Windows, F4 cycles through reference styles while editing a selected reference; on Mac, the equivalent shortcut is commonly Command+T, though keyboard mappings can vary by Excel version and system settings. You can also type the dollar signs directly.

Structured table references can make formulas easier to maintain as rows are added, provided the data is formatted as an Excel table. Check that the formula refers to the intended table columns and that headings are unique and clear. A reference to the wrong column can return a sensible-looking number while answering the wrong question.

A practical troubleshooting sequence

When a total looks wrong, work from the formula structure toward the data instead of rewriting everything at once. This makes it easier to find the one assumption that is failing.

Confirm the first argument is the range containing the numbers to add.
Match the dimensions of the sum range and every criteria range.
Verify each criterion is paired with the correct field and uses the intended operator.
Test whether the data is stored as expected: text versus numbers, valid dates versus date-like text, and clean text without unwanted spaces.
Check copied references for shifting ranges, and verify the expected result by filtering the source rows or adding the qualifying amounts manually.

Filtering the source data is a useful independent check, but make sure every filter matches the formula’s conditions. For a multiple-condition total, count the qualifying records first and then add their values. If the arithmetic agrees with the filtered rows but not the formula, inspect argument order and references. If the rows differ, focus on criteria and data types.

Frequently Asked Questions

Why does SUMIFS return 0 when matching rows exist?
Usually a criterion is not an exact match for the stored value. Check spaces and spelling, numeric values stored as text, date values stored as text, and whether all conditions apply to the intended columns. SUMIFS returns zero when no row meets every condition.

Can SUMIFS use more than one condition?
Yes. Add a criteria-range and criterion pair for each additional condition. A row contributes to the total only when it meets all the specified conditions.

Does the sum range have to be the same size as each criteria range?
Yes. Keep the ranges aligned and equal in row and column dimensions. A mismatch can produce an error or make the calculation unreliable.

How do I sum values between two dates?
Use one condition for dates on or after the start and another for dates before the next period’s start. This inclusive-start, exclusive-end method also handles times recorded during the final day.

What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition and places its criteria range before its condition. SUMIFS handles multiple conditions and places the sum range first. The similar names make argument order an easy mistake.

When to consider another approach

SUMIFS is a good fit for adding values in a rectangular data list based on conditions. If you need a count rather than a total, COUNTIFS is designed for multiple conditions. If you need to return matching records rather than add their values, filtering or a suitable lookup formula may be more appropriate. For a one-off check, applying worksheet filters and manually checking the visible values can help diagnose the source data.

Whichever method you choose, make the expected result easy to verify: keep one record per row, use clear column headings, store dates as dates and quantities as numbers, and avoid mixing categories in a single field. Good data structure prevents many apparent SUMIFS errors before the formula is written.

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.