SUMIF Function Not Working in Excel? Fix Common Errors

If SUMIF is returning zero or the wrong total, first check that the criteria range and sum range cover the intended rows, then confirm that the criterion matches the underlying cell values—not just how they look. Hidden spaces, numbers stored as text, an incorrect comparison condition, and dates treated as text are frequent causes; checking those in order usually reveals the problem.

What SUMIF does—and what it expects

SUMIF adds values that meet one condition. Its arguments are a range to check, a criterion to apply, and an optional range to sum. If you leave out the sum range, Excel adds the matching cells in the range being checked. If you provide a separate sum range, keep it aligned with the criteria range: the first checked row should correspond to the first value to add, and both ranges should cover the same rows.

For example, with regions in A2:A6 and sales amounts in B2:B6, use this layout: East / 120, West / 80, East / 150, east / 30, and North / 40. The formula is =SUMIF(A2:A6,"East",B2:B6). It returns 300 because Excel adds 120, 150, and 30. SUMIF is not case-sensitive, so “East” and “east” both match.

A useful first check is to click a cell that should match and inspect its value in the formula bar. The displayed text may conceal a trailing space or a different underlying value. Also check whether the formula points to the columns you intended; a correct criterion applied to the wrong column can produce a convincing but incorrect result.

Check the ranges and row alignment

A common mistake is selecting a criteria range and sum range that start on different rows, or accidentally ending one range early. Excel can still calculate when the supplied ranges have different dimensions, but it may pair values in a way you did not intend. Use ranges with matching boundaries and verify that each criterion cell corresponds to the amount on the same row.

Confirm that the criteria range contains the labels or values you want Excel to test.
Confirm that the sum range contains the numbers you want added—not a neighboring column.
Check the first and last row in both references, especially after inserting or deleting rows.
If the formula will be copied, consider whether its references should move. Add dollar signs to lock a reference when needed, or use an Excel Table so formula references can expand as rows are added.

For a quick test, temporarily select a small set of known rows and calculate the expected total by hand. If that smaller calculation works, expand the references carefully. If your source data is an Excel Table, structured references can make the formula easier to read, but the table column names must match the actual headers.

Make sure the criterion matches the data

Text criteria need to match the cell contents. A space at the end of “East ” is different from “East,” even though the difference can be hard to see. Nonbreaking spaces copied from websites are especially troublesome because they do not behave like ordinary spaces. Try editing one affected cell, deleting the content, and typing it again. For a large imported list, clean the source data in a helper column or use Find and Replace after checking exactly which character is present.

For a criterion that refers to a cell, use the cell reference rather than putting the reference name inside quotation marks. With region names in A2:A6, amounts in B2:B6, and the desired region typed in E2 as East, use =SUMIF(A2:A6,E2,B2:B6). This returns 300 for the example data above. If you wrote “E2” as literal text in the criterion, Excel would look for cells containing the characters E2 instead of reading the value in that cell.

SUMIF text matching is generally case-insensitive, so changing capitalization will not separate East from east. If case-sensitive matching is essential, SUMIF alone is not the right tool; a formula using other functions or a helper column may be needed.

Use comparison criteria correctly

Criteria can be exact values, text, cell references, or comparisons such as greater than and less than. When the comparison symbol is part of a text criterion, put the symbol and value together in quotation marks. With numbers in B2:B6 from the example, =SUMIF(B2:B6,">100") returns 270: only 120 and 150 are greater than 100.

If the threshold is in a cell, join the comparison operator to the cell reference rather than typing the reference as part of a quoted string. This is also useful when a threshold changes regularly. Check the boundary carefully: greater than 100 excludes 100, while greater than or equal to 100 includes it. A misplaced operator can look like a calculation error when the formula is doing exactly what its criterion specifies.

Text criteria can use wildcards. An asterisk represents any number of characters, while a question mark represents one character. A tilde escapes a wildcard when you want to search for a literal asterisk or question mark. With labels in A2:A5 reading North Region, North Depot, South Region, and North, and amounts in B2:B5 of 50, 25, 70, and 10, respectively, =SUMIF(A2:A5,"North*",B2:B5) returns 85. The pattern matches all three labels that begin with “North.”

Investigate numbers stored as text

A cell can look numeric while holding text, often after data is imported from a CSV file, copied from a website, or entered with a leading apostrophe. Number formatting alone does not convert text into a number. If the sum range contains text-formatted numbers, SUMIF may skip them rather than add them; if the criteria range contains text-formatted numbers, comparisons can also behave differently from what you expect.

Look for warning indicators, left-aligned values, or an apostrophe visible in the formula bar, but do not rely on alignment alone because formatting can change it. Select a sample cell and check whether Excel recognizes it as a number. Depending on the source, you can convert the column using the warning menu’s Convert to Number option, or use Data > Text to Columns and finish the import with the appropriate settings. Keep a copy of the original data before bulk conversion, particularly when leading zeros might be meaningful codes rather than unwanted formatting.

Also inspect the sum range for formulas returning text, blank strings, or error values. A value that looks like a number but is produced as text is not equivalent to a numeric value. Fixing the source column is usually more dependable than trying to compensate in every summary formula.

Check dates, errors, and calculation settings

Excel stores real dates as serial numbers and displays them using date formatting. Imported dates may instead be text, so a criterion that compares dates will not necessarily match them. Confirm that the source cells are actual Excel dates by changing a sample cell’s number format temporarily; a real date will display as a serial number when formatted as General. If it remains text, convert the source values before relying on date criteria.

To total January 2026 sales, with real dates in A2:A4 and amounts in B2:B4, assume the rows are Jan 5 / 100, Jan 20 / 150, and Feb 3 / 200. Use =SUMIFS(B2:B4,A2:A4,">="&DATE(2026,1,1),A2:A4,"<"&DATE(2026,2,1)). The result is 250. SUMIFS is appropriate here because January requires two conditions: on or after January 1 and before February 1. Using the first day of the next month as an exclusive boundary also includes dates that contain times on January 31.

If a result does not update after source data changes, check Formulas > Calculation Options and select Automatic. In Manual mode, Excel may keep showing an older result until recalculation. If the formula displays as text in the cell instead of calculating, check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign; change the format to General, then re-enter the formula.

Understand zero results and error messages

A result of zero does not always mean the formula is broken. It can mean no criteria values matched, or that matching rows contain zero, blanks, or text rather than numeric amounts. Check for hidden spaces, mismatched number types, an incorrect column, and a criterion that is more restrictive than intended. Temporarily testing one known label can help distinguish a matching problem from a summing problem.

An error such as #VALUE! may point to an invalid reference, incompatible data, or an error in a referenced cell. Inspect the cells in both ranges for errors and verify that the formula has balanced parentheses and quotation marks. If the formula itself appears literally in the grid, it may be stored as text or Show Formulas may be enabled; use Formulas > Show Formulas to check that display setting.

Regional settings can affect how formulas are entered. The examples here use commas between arguments, as requested for standard Excel formula syntax, but some installations expect semicolons because of their list-separator settings. If Excel rejects an otherwise valid formula at entry, check your regional separator setting and use the separator Excel expects. This changes punctuation, not the underlying SUMIF logic.

When SUMIFS or another approach is a better fit

SUMIF handles one condition. Use SUMIFS when the total must meet multiple conditions, such as a region and a date range, as in the January example. Note that SUMIFS puts the sum range first, unlike SUMIF. Reversing those arguments is a common reason a multiple-condition formula returns an unexpected result.

If you need a case-sensitive total, complicated text cleanup, or conditions that SUMIF cannot express clearly, consider a helper column or a different formula design. A PivotTable is often easier for exploring totals by several categories, dates, or other fields without writing a separate formula for every group. The best option depends on whether the summary must update inside the grid, support multiple criteria, or be easy to rearrange for analysis.

Frequently Asked Questions

Why does SUMIF return zero when the matching value is visible?
Check for leading or trailing spaces, nonbreaking spaces, text-versus-number differences, and a reference to the wrong column. The visible text may not be identical to the underlying cell value.

Does SUMIF distinguish uppercase and lowercase?
No. SUMIF text criteria are not case-sensitive, so “East” and “east” match the same text values. Use a case-sensitive alternative or helper column if capitalization must distinguish categories.

Can SUMIF add values based on a date?
Yes, provided the dates are real Excel date values and the criterion is constructed correctly. Use SUMIFS when a date period needs both a start and an end condition.

Why does SUMIF ignore some amounts?
The amounts may be stored as text, the cells may be blank, or the relevant rows may not meet the criterion. Check a skipped cell’s underlying value and confirm it is numeric.

What is the difference between SUMIF and SUMIFS?
SUMIF applies one condition; SUMIFS applies multiple conditions. Their argument order differs: SUMIF begins with the criteria range, while SUMIFS begins with the sum range.

A reliable troubleshooting order

Start with the formula’s intended columns and row boundaries, then test one known matching row. Next, inspect the criterion and source cells for spaces, text-formatted numbers, or text-formatted dates. Verify comparison operators and wildcard patterns, check calculation mode, and finally consider whether the task needs SUMIFS rather than SUMIF. Keeping a small set of known input rows and an expected total makes it much easier to tell whether a later change fixed the actual cause.

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.