SUMIF Formula Not Working? Fix Common Excel Errors

If SUMIF is returning zero or the wrong total, check that its criteria range, criterion, and sum range match the data you intend to evaluate; hidden spaces, text-formatted numbers, and mismatched ranges are frequent causes. With fruit names in A2:A6 and sales in B2:B6, =SUMIF(A2:A6,"Apples",B2:B6) returns 37 when the three Apples rows contain 12, 15, and 10.

How SUMIF works

SUMIF adds values that meet one condition. Its arguments are the cells Excel checks, the condition to apply, and—optionally—the cells to add. The criteria range and sum range should line up row by row: if a match is found in row 4 of the criteria range, Excel adds the value from row 4 of the sum range.

For the opening example, A2:A6 contains Apples, Pears, Apples, Apples, Pears, while B2:B6 contains 12, 8, 15, 10, 5. Excel finds Apples in rows 2, 4, and 5, then adds 12 + 15 + 10 for a result of 37. SUMIF does not distinguish uppercase from lowercase, so “APPLES” can match “Apples.”

The third argument is optional. If you leave it out, Excel sums the criteria range itself, which is useful when the cells being tested are also the numbers to add. If you need to test more than one condition—for example, a product and a region—SUMIFS is generally the more suitable function.

Check the ranges and their alignment

A common cause of an incorrect total is selecting the wrong cells or starting the criteria and sum ranges on different rows. If the category labels are in A2:A100 and the amounts are in B2:B100, both ranges should cover the same records. A range that starts at row 2 and another that starts at row 3 can associate a match with the wrong amount.

Confirm that the first range contains the values SUMIF should inspect, such as product names or dates.
Confirm that the sum range contains the numbers you mean to add, not another column of labels or unrelated figures.
Check the top and bottom rows of both ranges. A range that stops early silently leaves later records out.
When copying a formula, check whether the references shifted. Use dollar signs to lock a range if it must stay fixed when copied.

For example, with categories in A2:A6 and amounts in B2:B6, the following formula totals amounts for Apples. The result is 37 for the example data above:

=SUMIF(A2:A6,"Apples",B2:B6)

Excel permits criteria and sum ranges of different sizes, but relying on that can make a formula difficult to interpret and prone to unintended results. Matching their dimensions is the clearest, safest approach. Also make sure the formula points to the current sheet or workbook; a valid range on the wrong sheet can produce a plausible but irrelevant total.

Make sure the criterion matches the data

Text criteria need to match the cell contents, apart from capitalization. Extra spaces, punctuation, abbreviations, or a different spelling can prevent a match. A cell that appears to contain “Apples” might actually contain a trailing space, or a nonbreaking space copied from a website. Click a sample source cell and inspect its contents in the formula bar; compare it with the criterion rather than relying only on how the cell looks.

Criteria can also use comparison operators such as greater than, less than, or equal to. Put a comparison operator and value together inside quotation marks when typing them directly. For a threshold stored in another cell, join the operator to that cell reference with the ampersand. With amounts in B2:B6 and the threshold 100 in E2, this formula sums values at least 100. If B2:B6 contains 40, 100, 125, 90, and 200, the result is 425:

=SUMIF(B2:B6,">="&E2,B2:B6)

In this HTML, & represents the ampersand operator; Excel reads the formula as =SUMIF(B2:B6,">="&E2,B2:B6) when the character is entered in the formula bar. If a criterion comes from a cell, check that the cell contains the expected value and that the operator is joined to it correctly.

Look for numbers stored as text

A value can look like a number and still be stored as text. This can happen after importing a CSV file, pasting data from a web page, or adding an apostrophe before an entry. Number formatting alone does not convert text into a numeric value. One clue is inconsistent alignment, though alignment can be changed manually and is not conclusive.

If SUMIF is meant to test numeric amounts, check a few source cells and try changing their format or using Excel’s available conversion prompts. The exact conversion approach depends on how the data was imported; options include Text to Columns or multiplying text-formatted numeric values by 1 in a separate helper column. Keep an untouched copy of imported data until you confirm the conversion worked.

Text-formatted numbers in the sum range can also be overlooked or treated differently from genuine numeric values. Verify the source values, not just the displayed total. Avoid “fixing” a formula by changing its criterion until you know whether the underlying cells are numbers or text.

Handle dates as dates

Excel stores real dates as serial numbers and displays them using a date format. A date typed or imported as text may look identical but fail to match a date criterion as expected. Check that the source column contains actual Excel dates, and apply a date format to confirm the displayed values are sensible. Formatting text as a date does not itself convert the text.

For a date interval, SUMIFS is often clearer because it can apply both a start and an end condition to the date range. With dates in A2:A4 and amounts in B2:B4, suppose the rows are January 5, January 20, and February 2, 2026, with amounts 10, 15, and 20. This formula totals dates in January 2026 and returns 25:

=SUMIFS(B2:B4,A2:A4,">="&DATE(2026,1,1),A2:A4,"<"&DATE(2026,2,1))

The upper bound is less than February 1, so the formula includes all times on January 31 too if the date cells contain time values. In the formula bar, use the actual ampersand and less-than characters shown by the HTML entities above.

Use wildcards carefully

When matching text, an asterisk (*) represents any number of characters, and a question mark (?) represents one character. This can help when entries contain extra text, but it can also make a criterion broader than intended. For example, a criterion of “App*” can match “Apples” and “Appetizers.” Wildcards are for text matching, not a substitute for checking whether a numeric or date value is stored correctly.

With descriptions in A2:A5 and amounts in B2:B5, if the descriptions are “North Apples,” “South Apples,” “North Pears,” and “Apple Juice,” the following formula sums entries whose text ends with Apples. If the corresponding amounts are 12, 8, 6, and 4, it returns 20:

=SUMIF(A2:A5,"*Apples",B2:B5)

If you need to match a literal asterisk or question mark rather than use it as a wildcard, place a tilde before that character in the criterion. This is useful for unusual labels, but most ordinary text criteria do not need wildcard symbols.

Diagnose zero totals and wrong results

A zero result does not necessarily mean SUMIF is broken. It can mean no cells met the criterion, the sum range contains no usable numbers for matching rows, or the formula refers to the wrong place. Work through a small sample of the source data before rebuilding the whole formula.

Select a cell that should match and compare its full contents with the criterion. Look for spaces or unexpected characters.
Check that the matching row’s sum cell contains a real number and that the formula includes that row.
Temporarily test a simple, known criterion against a few rows. If that works, add the full range or more complex condition back in stages.
Check for a leading apostrophe or imported text values, especially in numeric and date columns.
Confirm Excel is set to recalculate. In Microsoft 365 for Windows, use Formulas > Calculation Options > Automatic. If calculation is set to Manual, formulas may not update when source data changes; press F9 to recalculate.
Review the formula bar for a missing quote, misplaced comma, or shifted range reference. Excel installations using regional settings that require semicolons may need semicolon separators instead of commas.

If a total is too high, look for a broad wildcard, duplicate source rows, or a sum range that includes extra records. If the result is correct until new rows are added, the formula may use a fixed range that does not include them. Consider using an Excel Table so formulas can refer to a data column that expands with the table, or update the range when the data grows.

When to use SUMIFS instead

SUMIF handles one condition; SUMIFS handles multiple conditions. The argument order differs: SUMIF puts the criteria range first and the sum range last, while SUMIFS puts the sum range first, followed by pairs of criteria ranges and criteria. Mixing up those orders is a common source of incorrect results.

With products in A2:A6, regions in B2:B6, and amounts in C2:C6, suppose the rows are Apples/East/12, Pears/East/8, Apples/West/15, Apples/East/10, and Pears/West/5. This formula totals Apples in the East region and returns 22:

=SUMIFS(C2:C6,A2:A6,"Apples",B2:B6,"East")

Use SUMIF when one condition is enough; use SUMIFS when the total must satisfy two or more conditions, such as product plus region or date range plus department. If the requirement is to count matching rows rather than add amounts, COUNTIF or COUNTIFS may be the better fit. Choosing the function that matches the question makes the formula easier to check.

Frequently Asked Questions

Why does SUMIF return 0 when the matching text is visible?
Check for trailing or leading spaces, nonprinting characters, a criterion that does not match the full entry, and an incorrect range. Also confirm the corresponding sum-range cells contain numbers rather than text.

Does SUMIF distinguish uppercase and lowercase?
No. SUMIF’s text matching is not case-sensitive, so different capitalization alone does not prevent a match. If capitalization must determine which rows qualify, a different approach is needed.

Can SUMIF use a condition from another cell?
Yes. For a comparison such as “greater than” a threshold in another cell, join the operator to the cell reference with an ampersand. Confirm the referenced cell contains the intended threshold and use the correct comparison operator.

Why does SUMIF stop including new rows?
The formula may use a fixed range that ends above the added data. Extend the range or use an Excel Table whose data references expand as rows are added.

Can SUMIF add values between two dates?
For a date interval, SUMIFS is usually the practical choice because it can apply a lower and upper date condition to the same date range. Make sure the source dates are real Excel date values, not text that merely looks like dates.

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.