SUMIFS Not Working? Fix Common Excel Errors

SUMIFS usually fails because its sum range and criteria ranges do not line up, a criterion does not match the data exactly, or dates and numbers are stored in a different format than expected. Check the ranges first, then verify each criterion against the underlying cell values; if the formula returns an error, inspect the range sizes and formula syntax.

How SUMIFS works

SUMIFS adds values that meet one or more conditions. Its first argument is the range to add; the remaining arguments come in pairs: a range to check and the condition to apply to that range. When there are multiple pairs, a row must meet every condition to be included. This is an AND test, not an OR test.

For example, imagine a small sales table with item names in A2:A6, regions in B2:B6, and amounts in C2:C6. The rows are Coffee/East/12, Tea/East/8, Coffee/West/10, Coffee/East/5, and Tea/West/7. With that layout, this formula totals Coffee sales in the East:

With item names in A2:A6, regions in B2:B6, and amounts in C2:C6, enter =SUMIFS(C2:C6,A2:A6,"Coffee",B2:B6,"East"). It returns 17, adding 12 and 5.

The order matters: SUMIFS expects the sum range first, then a criteria range and its criterion, followed by any additional range-and-criterion pairs. Reversing a pair can produce a plausible but incorrect answer, so compare each range with the condition immediately after it.

Check the ranges and their dimensions

The most important structural check is that the sum range and every criteria range cover corresponding cells. If the amount range is C2:C100, but a criteria range is B2:B99, the ranges are not aligned. Excel may return #VALUE! when range dimensions differ, or the formula may omit or include the wrong records if the references point to unintended rows.

Look closely at the starting and ending row numbers, especially after copying a formula down or adding data. A common mistake is including a header in one range but not another, or using a full-column reference for one argument and a shorter range for another. Use matching shapes, such as C2:C100, A2:A100, and B2:B100. If a formula is copied, decide which references should move and which should stay fixed; dollar signs lock a reference when copied.

Also check that the ranges refer to the intended sheet. A reference such as Sales!C2:C100 sums amounts on the Sales sheet, while an unqualified A2:A100 refers to the current sheet. A formula can be syntactically sound and still give an unexpected result if its ranges point to different tables.

Make sure the criteria match the data

Text criteria generally need to match the cell contents. A trailing space in a cell, such as “East ”, is different from “East”; so are different spellings, abbreviations, and punctuation. SUMIFS is not case-sensitive for ordinary text comparisons, so “coffee” and “Coffee” are not usually the source of a mismatch. To inspect suspicious values, select a cell and look at the formula bar, or use Find and Replace carefully to locate unexpected spaces.

Criteria can be typed directly into a formula or linked to a cell. A cell reference makes the calculation easier to change, but the referenced value still has to match the data. For an exact item name held in E2 and region held in F2, use this layout:

With item names in A2:A6, regions in B2:B6, amounts in C2:C6, the selected item in E2, and the region in F2, enter =SUMIFS(C2:C6,A2:A6,E2,B2:B6,F2). If E2 contains Coffee and F2 contains East, the result is 17.

For conditions such as greater than or equal to a number, put the operator and value together as a criterion. The operator is written in quotation marks and joined to a cell reference with an ampersand. For instance, if the minimum amount is in E2:

With amounts in C2:C6 and the minimum amount in E2, enter =SUMIFS(C2:C6,C2:C6,">="&E2). If E2 is 10, the formula returns 22 because 12 and 10 meet the condition. The HTML entity shown for the ampersand represents the normal ampersand character in the Excel formula.

Dates need special attention

Excel stores valid dates as serial numbers, even though cells display them in a date format. A criterion that looks like a date may not match if the source cells contain text instead of real dates. Date formats can also differ by regional settings, making a typed value ambiguous. Test a suspicious date by changing its number format temporarily or checking whether Excel recognizes it as a date.

For a date interval, use a start boundary and an end boundary rather than trying to compare dates as text. A useful pattern is greater than or equal to the first date and less than the day after the last date. This includes every time on the final day if the source values contain timestamps. With transaction dates in A2:A6, amounts in C2:C6, and start and end dates in E2 and F2, the pattern is:

With dates in A2:A6, amounts in C2:C6, a start date in E2, and an inclusive end date in F2, enter =SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<"&F2+1). The total depends on the actual dates and amounts in those cells; the formula includes dates from E2 through the whole calendar day in F2.

If you type dates directly into formulas, DATE is safer than relying on a locale-dependent date string. In Excel for Microsoft 365 on Windows, formulas normally use commas between arguments, but some regional settings use semicolons. If Excel rejects a formula that otherwise looks correct, check the list separator configured for your Excel installation.

Check numbers, text, blanks, and hidden characters

A cell can look numeric while containing text. Numbers pasted from a website or imported from a text file may be stored as text, sometimes with leading apostrophes, spaces, or nonbreaking spaces. SUMIFS will not treat every text value as an ordinary numeric amount to add. Select a sample, inspect the formula bar, and check whether numbers align or behave like text. Excel’s warning indicator may offer Convert to Number; for larger imports, use Data > Text to Columns or clean the source data before summing.

Criteria can also miss values containing invisible characters. Nonbreaking spaces are especially troublesome because they look like ordinary spaces but are not removed by every basic cleanup method. If the data comes from an external system, compare a value with a manually retyped version, or use a helper column to clean and standardize the source. Avoid changing the displayed format alone: formatting a text value as Number does not necessarily convert it into a numeric value.

Blanks require thought, too. A criterion that refers to an empty cell is not always the same as a cell containing a formula that returns an empty string. Similarly, a cell that appears blank may contain a space. If the formula should include blank categories, test a few known rows individually and inspect their contents instead of assuming the display tells the whole story.

Understand wildcards and comparison criteria

For text criteria, an asterisk (*) matches any sequence of characters and a question mark (?) matches one character. These can be useful for categories that contain extra wording. For example, with descriptions in A2:A6 and amounts in C2:C6, the criterion “Coffee*” can match text that begins with Coffee. Wildcards are not appropriate when you need a strict whole-cell match or when the source is numeric.

To search for a literal asterisk, question mark, or tilde, precede it with a tilde (~). Without that escape, Excel treats * and ? as wildcard characters. Criteria can also use operators such as >, <, >=, <=, and <>. For conditions built from cell values, join the operator and reference with &, as shown earlier. Each additional SUMIFS condition narrows the result because all conditions must be true for a row to count.

If you need OR logic—for example, Coffee sales in either East or West—one SUMIFS with both region conditions will not express that request, because no row can have both region values at once. You can add separate SUMIFS results, or use a different formula design. Be careful not to add overlapping conditions twice, since that can double-count rows.

Diagnose zero totals and formula errors

A result of 0 is not automatically an error. It may mean no rows meet all the conditions, or that the matching sum cells are blank or contain text rather than numbers. Temporarily remove one condition at a time to identify which one eliminates the matches. You can also filter the source table for the same criteria and confirm whether the expected rows are present.

Errors provide clues. #VALUE! often points to incompatible range sizes or an error value in a referenced calculation; inspect the ranges and the source cells. #NAME? can indicate a misspelled function name or text criterion missing quotation marks. A formula displayed literally in the cell rather than calculating may mean the cell is formatted as Text, the entry begins with an apostrophe, or Show Formulas is enabled. Change the cell format to General, re-enter the formula, and check Formulas > Show Formulas if the whole sheet displays formulas.

When a formula returns an unexpected nonzero total, verify the arithmetic with a small, known subset. Filter the records to the same criteria, add the visible amounts, and compare that sum. Also check whether a criterion uses a broad wildcard or a comparison boundary that includes more rows than intended. Do not wrap the formula in IFERROR until the underlying cause is understood; doing so can hide a real data or reference problem.

A practical troubleshooting order

Use this sequence to narrow down the cause without rebuilding the workbook:

Confirm the formula begins with SUMIFS and that the sum range is its first argument.
Match the start and end rows of every criteria range to the sum range.
Test each criterion against the actual cells, including spaces, spelling, and data type.
For dates and numeric thresholds, use comparison operators joined to cell references.
Remove criteria one at a time, then add them back to find the condition that changes the result.
Check source cells for errors, text-formatted numbers, and dates stored as text.

If the table grows regularly, converting it to an Excel table can make references easier to maintain: select a cell in the data and choose Insert > Table. Structured references can expand as rows are added, but make sure the sum column and criteria columns still refer to the intended fields. In Excel for Mac, the same SUMIFS logic applies; ribbon labels and some dialogs can differ slightly from Windows. Excel for the web also supports SUMIFS, although some editing and import tools may be more limited than the desktop application.

Frequently Asked Questions

Why does SUMIFS return 0 when matching rows are visible?
Visible text may contain extra spaces or differ from the criterion, and displayed dates or numbers may be stored as text. Check the underlying values, test each condition separately, and verify that all ranges cover corresponding rows.

Does SUMIFS use AND or OR logic?
It uses AND logic across its criteria pairs: a row is included only when every condition is met. For OR logic, such as either of two regions, add separate SUMIFS calculations or use another formula approach, taking care to avoid double-counting.

Why does SUMIFS show #VALUE!?
A frequent cause is that the sum range and one or more criteria ranges have different dimensions. Check their row and column boundaries first, then inspect the referenced cells for existing errors.

Can SUMIFS match part of a text value?
Yes. Use * for any number of characters or ? for one character in a text criterion. Precede a literal wildcard with ~ if you want Excel to search for the character itself.

Why does SUMIFS ignore some dates?
The source dates may be text rather than Excel date values, the criterion may use an ambiguous date string, or timestamps may fall outside the boundary you chose. Use real date values and clear start and end comparisons; for a full final day with timestamps, use a less-than comparison against the following day.

Is SUMIFS case-sensitive?
No. Standard text comparisons in SUMIFS are not case-sensitive. If capitalization seems to be the difference, look instead for extra spaces, different punctuation, or other characters that are not visible in the cell.

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.