SUMIF Not Working in Excel? How to Find and Fix Errors

If SUMIF is not working in Excel, first check that the criteria range and sum range cover the intended rows, then confirm the criterion matches the data exactly and that the values to add are real numbers. A wrong total or zero result is usually caused by a range mismatch, hidden spaces, text-formatted numbers, or a criterion written in the wrong form.

How SUMIF is supposed to work

SUMIF adds values when a corresponding cell meets one condition. Its syntax is SUMIF(range, criteria, [sum_range]): range contains the cells Excel checks, criteria describes what must match, and the optional sum_range contains the values to add. If you omit sum_range, Excel adds the cells in range itself.

For example, with product names in A2:A6 and amounts in B2:B6, and these rows: Apples 12, Pears 8, Apples 13, Oranges 5, and Pears 4, the formula =SUMIF(A2:A6,"Apples",B2:B6) returns 25. Excel checks each product cell and adds the amount from the corresponding row when it finds Apples. This pairing is why the row positions of the two ranges matter.

SUMIF handles one condition. If you need to require two or more conditions at the same time, use SUMIFS instead; its argument order is different, with the sum range first. The function is available in current desktop Excel and Excel for the web, so a basic SUMIF formula does not require an add-in.

Check the ranges and their row alignment

A common source of a plausible but incorrect total is selecting ranges that start or end on different rows. If names are in A2:A100 and amounts are in B2:B100, both ranges should normally cover rows 2 through 100. If one range starts at row 1, or stops at row 99, values can be paired with the wrong records or omitted.

Confirm that the criteria range contains the labels, dates, or numbers you want Excel to test.
Confirm that the sum range contains the numeric amounts to add.
Check that both ranges have the same number of rows and that their first rows represent the same record.
Look at the last included row. New data added below a fixed range will not be included automatically.

Click the formula cell and inspect the colored range outlines in the worksheet. This makes it easier to spot a range that misses a row or includes a heading. If the data grows regularly, converting it to an Excel table with Insert > Table can make formulas easier to maintain; table references expand as rows are added. Still verify the formula’s references if the result suddenly stops reflecting new records.

Make the criteria match the data

Text criteria are generally not case-sensitive, so Apples and APPLES match in ordinary SUMIF use. However, spelling, punctuation, and extra spaces can matter. A criterion such as Apples will not match a cell containing Apple, or a value with an unwanted trailing space. A leading space can be just as hard to notice when scanning a worksheet.

Check a few source cells by selecting them and looking in the formula bar. For imported or copied data, use Find and Replace to inspect unusual spaces, or clean a copy of the source data before relying on a total. Do not remove spaces blindly if they are meaningful parts of names or codes. Also check for nonbreaking spaces in data copied from websites; these can look like ordinary spaces but are different characters.

Criteria that use comparison operators need the operator inside quotation marks. When the threshold is stored in another cell, join the operator and cell reference with an ampersand. With scores in B2:B5 containing 80, 120, 160, and 90, =SUMIF(B2:B5,">100") returns 280 because 120 and 160 are above 100. With the same scores and the threshold 100 in E1, =SUMIF(B2:B5,">"&E1) also returns 280. Writing the criterion as a quoted operator followed by a cell reference without joining them is a common mistake.

For partial text matches, SUMIF supports wildcards: an asterisk represents any number of characters, and a question mark represents one character. With region labels in A2:A4 containing North, Northeast, and South, and sales in B2:B4 of 40, 30, and 20, =SUMIF(A2:A4,"North*",B2:B4) returns 70. Use a tilde before a wildcard character when you need to match that character literally rather than use it as a wildcard.

Look for numbers stored as text

Cells can look numeric while being stored as text, especially after a CSV import, a copy from a web page, or an entry with a leading apostrophe. Text-formatted values may not behave as expected when SUMIF tries to add them. A quick visual check is to select a suspect cell: Excel may show a warning indicator, or the value may be left-aligned while neighboring numbers are right-aligned. Alignment is only a clue, not a reliable test by itself.

To correct a column, select it and use the warning menu if Excel offers Convert to Number. Another option is Data > Text to Columns, then finish the wizard with the appropriate defaults; this often converts numeric text without changing the displayed values. You can also multiply a copied column by 1 in a temporary area, then paste the converted values back if appropriate. Keep a copy first when the imported data contains identifiers such as postal codes, where leading zeros may be significant.

Formatting a text value as Number does not necessarily convert it into a numeric value. Number formatting changes how a value is displayed, while conversion changes the underlying value Excel calculates with. Check both the cells being summed and the cells used as criteria, especially when numeric-looking criteria are not matching numeric source values.

Check dates, blanks, and other unusual criteria

Excel stores valid dates as serial numbers, but imported dates may be text. If a date criterion produces zero, check whether the source cells are genuine Excel dates and whether the criterion cell contains a date rather than a text string that only looks like one. Date display formats can differ while underlying dates are equivalent, so changing the display format alone will not repair text dates.

For instance, with dates in A2:A4 of January 1, January 15, and December 31, 2026, amounts of 100, 80, and 50 in B2:B4, and a genuine January 15, 2026 date in E1, =SUMIF(A2:A4,">="&E1,B2:B4) returns 80. The January 1 row is before the threshold, while January 15 meets it. If your regional settings interpret typed dates differently, enter dates using the date picker or a format Excel recognizes in your installation, then verify the resulting cell value.

Blank criteria have their own behavior: a blank criterion can match blank cells, while criteria such as "<>" are used to test for nonblank entries. If blank-looking cells behave unexpectedly, they may contain formulas returning an empty string or spaces, rather than being truly empty. Decide whether those entries should count before changing the formula.

Diagnose zero, a wrong total, or an error message

A result of zero often means Excel found no matching criteria, or the corresponding sum cells are blank or text. A total that is too high can indicate an overly broad wildcard, duplicated rows, or a sum range that includes unintended records. A total that is too low may mean the range excludes new rows, a value is stored as text, or some labels differ slightly.

If Excel displays an error, inspect the formula’s punctuation and references. A missing quotation mark around a text criterion, an extra parenthesis, or a misspelled function name can prevent calculation. Some regional configurations use semicolons rather than commas as formula separators; if Excel rejects a correctly structured example using commas, use the separator expected by your Excel settings. Do not change the logic of the criterion while troubleshooting a separator issue.

Use the formula bar to review the complete formula, then test a small part of the data manually. Filter the criteria column for the value you expect and add the visible matching amounts independently. This helps distinguish a formula-construction problem from a source-data problem. If filtering reveals variants such as North, North , and North-East, the data itself explains why an exact criterion can miss rows.

Check calculation settings and refresh the result

Excel can be set to Manual calculation, in which case formulas may not update immediately after source cells change. In Microsoft 365 for Windows, check Formulas > Calculation Options > Automatic. If the workbook is in Manual mode, switch to Automatic when that is appropriate for your workflow, or recalculate deliberately. Press F9 to calculate formulas in open workbooks on Windows. On Mac, the calculation shortcut can depend on the keyboard and function-key settings; use the Formulas ribbon or Excel’s Help to confirm the shortcut for that setup.

Calculation mode is worth checking when the formula was correct earlier but its displayed answer has not changed after editing a value. It is less likely to explain a consistent zero from the first calculation. Also check whether the formula cell is formatted as Text: if Excel displays the formula characters instead of a result, change the cell format to General, re-enter the formula, and press Enter.

Choose SUMIF or SUMIFS for the job

Use SUMIF when one rule determines which values to add: sales for one product, expenses in one category, or orders after a date. Use SUMIFS when multiple rules must all be true, such as a product and a region, or a date range and a department. The key difference to remember is argument order: SUMIF begins with the criteria range, while SUMIFS begins with the sum range.

For example, if a report needs to add sales only when the product is Apples and the region is West, SUMIFS is the appropriate function because there are two conditions. Trying to combine both labels into a single SUMIF criterion will not create a reliable two-condition test unless the source data has already been combined into one matching field. A helper column can be useful in older or simpler workbooks, but for a direct multi-condition total, SUMIFS is clearer.

A practical troubleshooting order

Work from the simplest checks to the less visible causes so you do not rewrite a formula that is already logically correct. A useful sequence is:

Confirm the intended result by identifying a few matching rows and adding their amounts manually.
Check the criteria range, sum range, their start and end rows, and whether recent records are included.
Compare the criterion with the source values for spaces, spelling, punctuation, and wildcard behavior.
Verify that amounts are numeric and that date criteria use real Excel dates.
Check quotation marks, comparison operators, cell references, and the formula argument separator.
Confirm calculation mode and ensure the formula cell is not formatted as Text.

This order is useful because it separates formula logic from data quality. If the filtered matching rows add up to the expected amount but SUMIF does not, focus on the formula and calculation settings. If the filtered rows themselves are incomplete or inconsistent, correct or standardize the source data before changing the formula.

Frequently Asked Questions

Why does SUMIF return zero even though the value is there?
Check for extra spaces, a different spelling, mismatched text and numeric values, or an incorrect criteria range. A visually similar imported value may not be identical to the criterion. Filter the source column for the expected entry and inspect the matching cells.

Does SUMIF distinguish uppercase and lowercase?
No. Standard SUMIF text matching is not case-sensitive. If case must determine which rows qualify, use a different approach designed for case-sensitive comparisons rather than expecting SUMIF to distinguish uppercase from lowercase.

Can SUMIF add values based on a date?
Yes. Dates can be criteria, including comparisons such as on or after a date. Make sure the source dates are real Excel dates and join comparison operators to a date cell when the threshold is held in a cell.

Why does SUMIF work for old rows but ignore new ones?
The formula may use a fixed range that ends before the new rows. Extend the references or use an Excel table so added records are included in the data structure. Check the formula after adding rows rather than assuming its range expanded.

Why is Excel showing the formula instead of the total?
The cell may be formatted as Text, or the formula may have been entered with a leading apostrophe. Change the cell format to General, re-enter the formula, and confirm that Show Formulas has not been turned on from the Formulas tab.

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.