SUMIF Not Working? Fix Common Excel Errors

If SUMIF is not working in Excel, check that its criteria match the actual cell contents, that the sum range points to the values you intend to add, and that those values are numeric. Then check for date or text-format mismatches and confirm Excel has recalculated; these checks resolve most incorrect totals and formula errors.

How SUMIF works

SUMIF adds values associated with cells that meet one condition. Its three arguments are the cells to test, the condition to apply, and—optionally—the cells to add. If you leave out the sum range, Excel adds the matching cells in the range being tested. The condition can be text, a number, a comparison such as “greater than 100,” a wildcard pattern, or a reference to another cell.

For example, imagine a simple sales list with regions in column A and amounts in column B. A formula that looks for East in the region column and sums the corresponding amounts should include only the East rows. That basic logic is useful when troubleshooting: identify the condition, the cells Excel checks, and the cells it adds, then verify each one against the worksheet.

SUMIF checks one condition. If your task requires multiple conditions—for example, summing East-region sales of Apples—use SUMIFS instead. Its argument order differs: the sum range comes first, followed by each condition range and condition. Switching between the functions without changing the argument order is a common source of incorrect results.

Check the criteria and cell contents

A condition must match the underlying value, not just what a cell appears to show. A cell displayed as “East” might contain a trailing space, such as “East ”, or a nonbreaking space copied from a webpage. Those extra characters can prevent an exact text match. Click the cell and inspect the formula bar; compare the source text with the condition, and check for leading or trailing spaces.

For a basic example, assume A2:A6 contains East, West, East, East, West, while B2:B6 contains 120, 90, 80, 100, 60. The East rows total 300. With that layout, =SUMIF(A2:A6,"East",B2:B6) returns 300. SUMIF text matching is not case-sensitive, so “EAST” and “East” match; capitalization alone is not the problem.

If the condition is in a cell, reference that cell rather than retyping its value in the formula. With the same data in A2:A6 and B2:B6, and the word East in D2, =SUMIF(A2:A6,D2,B2:B6) also returns 300. Check that D2 contains the intended criterion and does not include invisible spaces.

For a text criterion, compare the exact cell contents, including spaces and punctuation.
For a numeric criterion, check whether the source values are real numbers or text that only looks numeric.
For a condition stored in a cell, make sure the formula references the correct cell and that the cell is not blank.

Look for numbers stored as text

Numbers imported from a CSV, copied from a website, or entered with a leading apostrophe may be stored as text. They can look like ordinary amounts, yet fail to behave like numbers in calculations. In SUMIF, this can matter in either the cells being tested or the values being added: a text value may not match a numeric criterion as expected, and text amounts in the sum range may not contribute to the total.

To investigate, select a suspect cell and check its alignment, warning indicator, and number format. Formatting a text value as Currency or Number does not necessarily convert the stored text into a number. Use Excel’s Convert to Number warning option when it appears, or use Data > Text to Columns and complete the conversion for a suitable imported column. Another option is to re-enter or convert the values in a separate column, then point SUMIF to that cleaned data. Confirm a few converted cells with a simple arithmetic check before relying on the result.

Be careful when cleaning data: a comma or period may serve as a decimal or thousands separator depending on the file’s regional conventions. A value such as “1,250” can be interpreted differently across locales. Verify that the converted amount is the intended amount rather than assuming that a changed alignment proves the conversion was correct.

Verify the criteria and sum ranges

The first range is where Excel looks for matches; the optional sum range is where it retrieves amounts to add. If you accidentally select the amount column as the criteria range, or the region column as the sum range, the formula can return zero or a nonsensical total even though the function itself is valid. Trace each reference in the formula bar and compare it with the column headings.

Keep the ranges the same shape and aligned to the same rows. For example, if the criteria range starts at row 2 but the sum range starts at row 3, Excel may pair a match with the wrong amount. Excel can accept differently sized ranges in some circumstances, but relying on that behavior makes formulas harder to audit and can lead to unexpected pairings. Use matching dimensions, such as A2:A100 and B2:B100, and include new rows when your data grows.

Also check whether a copied formula has shifted its references. Relative references change as a formula is copied; absolute references marked with dollar signs do not. If a formula worked in one row and fails in another, inspect the range boundaries. Excel’s formula bar and the colored reference outlines shown while editing can help reveal which cells each argument covers.

Handle comparison criteria and wildcards

Comparison operators must be included in the criterion. When the operator is fixed, put it inside quotation marks; when the threshold comes from a cell, join the operator to the cell reference. A criterion written as greater than 100 is different from a criterion that merely looks for the text “100.” Check the operator as well as the threshold—greater than and greater than or equal to are not interchangeable.

Wildcards are useful when text varies. An asterisk matches any number of characters, a question mark matches one character, and a tilde treats the following wildcard as a literal character. For example, if A2:A5 contains North Region, North Outlet, South, and North, and B2:B5 contains 40, 35, 50, and 25, respectively, =SUMIF(A2:A5,"North*",B2:B5) returns 100. It matches all three names starting with North. If a criterion contains an actual asterisk or question mark, prefix that character with a tilde in the criterion so Excel does not treat it as a wildcard.

Wildcards apply to text matching, not as a general substitute for numeric comparisons. If a condition should include all amounts above a threshold, use a comparison criterion rather than an asterisk pattern. When constructing a condition from a cell, confirm that the operator is joined to the reference correctly and that the referenced cell contains the threshold you expect.

Check dates and date boundaries

Excel stores genuine dates as serial numbers and displays them using a date format. Dates imported as text may look identical but will not behave like serial dates in a comparison. If a SUMIF date condition returns zero, check whether the source cells are actual dates, whether the criterion uses the same date system, and whether the date range includes times. A value with a time after midnight is later than the date at midnight, which can affect a boundary condition.

For a concrete example, assume A2:A4 contains actual Excel dates January 4, 2026; December 28, 2025; and January 15, 2026, while B2:B4 contains 25, 30, and 40. With that layout, =SUMIF(A2:A4,">="&DATE(2026,1,1),B2:B4) returns 65: it adds 25 and 40 for dates on or after January 1, 2026. DATE constructs a genuine date value, avoiding ambiguity from typing a date in a locale-dependent format.

If your data includes timestamps and you want a whole calendar day, use a boundary that accounts for the time component, or create a helper column containing date-only values. Do not assume that changing the cell’s display format converts text dates into real dates; validate the underlying values first.

Rule out calculation and visibility confusion

If the formula appears correct but the result has not changed after editing data, check the workbook’s calculation setting. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. In Automatic mode, Excel normally recalculates dependent formulas as inputs change. If the workbook is set to Manual, choose Calculate Now or switch it back to Automatic, taking care if the workbook is large and recalculation takes time. The same calculation options are available in Excel for Mac, though ribbon layout can vary by release.

A filtered or hidden row is another frequent source of confusion. SUMIF normally includes qualifying rows even if they are hidden by a filter or manually hidden. Filtering the table therefore does not make SUMIF recalculate as a visible-rows-only total. If you need a subtotal that responds to filtering while also applying a condition, plan for a different approach, such as a helper column or a formula designed to account for visible rows.

Finally, distinguish a wrong result from a formula error. A zero often means no cells met the criterion, the ranges are misaligned, or the values are stored in an unexpected form. An error displayed in the result may be caused by an error value in data the calculation touches or by a malformed reference. Read the exact error, inspect the referenced cells, and test the formula on a small known sample before changing several parts at once.

A practical troubleshooting sequence

Work through the checks in order so you can isolate the cause rather than rebuilding the formula blindly:

Confirm the function is meant to test one condition; use SUMIFS if the task has multiple conditions.
Read the condition and verify whether it should be text, a number, a comparison, a wildcard, or a cell reference.
Check the criteria range and sum range against the column headings and make sure they cover matching rows.
Inspect representative source cells for extra spaces, text-formatted numbers, text dates, or unintended time values.
Test the condition against one row you know should match and confirm that row’s amount is numeric.
Check calculation mode and remember that SUMIF includes qualifying hidden and filtered-out rows.

Changing one thing at a time makes the result easier to explain. If a cleaned helper column fixes the total, the issue was likely the source data rather than SUMIF’s syntax. If the formula works on a short test range but not the full column, compare the range boundaries and look for records outside the selected area.

Frequently Asked Questions

Why does SUMIF return 0 when matching entries are visible? The visible text may not exactly match the criterion, or the criteria cells may contain spaces or imported characters. The amount cells could also be text rather than numbers, or the formula could point to the wrong rows. Inspect the underlying values and test one known matching row.

Does SUMIF distinguish uppercase and lowercase? No. SUMIF’s text matching is not case-sensitive, so capitalization by itself will not separate two criteria. If you need case-sensitive conditional summing, SUMIF alone is not sufficient; you will need a different formula approach.

Why does SUMIF include rows hidden by a filter? SUMIF evaluates the referenced range, including qualifying rows hidden by filters or manual hiding. Filtering changes what is displayed, not which matching records SUMIF considers. Use a method specifically designed for visible-only totals if that is the requirement.

Can SUMIF use more than one condition? No. SUMIF handles one condition. For two or more conditions, use SUMIFS, remembering that SUMIFS places the sum range first and then takes condition-range and condition pairs.

Why does a date criterion fail even though the dates look right? The cells may contain text rather than Excel date values, or may include times that affect the boundary. Check the underlying data and use a date value—not an ambiguous text string—for the comparison.

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.