SUMIF Not Working? Fix Excel Criteria, Ranges, and Results

If SUMIF is not working, check that the criteria really matches the cells in your criteria range and that your sum range covers the corresponding values. In Excel for Microsoft 365 on Windows, a basic formula looks like =SUMIF(A2:A6,"East",B2:B6): it adds values in B2:B6 wherever the cell in the same row of A2:A6 is East.

Start with a quick diagnosis

Before rebuilding the formula, check what result Excel gives. A zero often means no criteria cells matched, or the matching rows contain values that Excel does not recognize as numbers. A total that is too large or too small more often points to a range that starts or ends on the wrong row, a criterion that is too broad, or values stored in an unexpected format.

Try these quick checks first:

Click the formula cell and confirm that it begins with = and uses the intended function, SUMIF.
Compare the criteria range and sum range: the first data row in each should refer to the same record.
Inspect a cell that should match. Look for an extra space, a different spelling, or a number stored as text.
Temporarily replace a criterion-cell reference with a criterion you can see in the data, or use a cell reference whose content you have checked.
Check whether Excel is set to calculate automatically if the formula appears not to update after you change the data.

For a useful test, filter or sort the source data by the criterion and add the visible matching amounts yourself. That gives you a small independent total to compare with SUMIF, and can reveal whether the problem is in the formula or the source data.

How SUMIF works

SUMIF has this structure: =SUMIF(range,criteria,[sum_range]). The range is where Excel looks for the condition. The criteria tells Excel what to look for. The optional sum_range contains the numbers to add. If you leave out sum_range, Excel adds numbers from range itself.

For example, with regions in A2:A6 and sales in B2:B6, suppose the rows contain East 120, West 80, East 150, North 50, and East 100. The formula =SUMIF(A2:A6,"East",B2:B6) returns 370: it adds 120, 150, and 100. The criteria range and sum range must describe corresponding rows. Keeping their dimensions and starting and ending rows aligned is the safest approach.

The criteria can be typed into the formula or read from a cell. With the same regions in A2:A6 and sales in B2:B6, and the word East in D2, =SUMIF(A2:A6,D2,B2:B6) also returns 370. Referencing a cell is convenient when the condition changes often; typing the criterion is handy for a fixed, one-off calculation.

Check the criteria for exact matches

Text criteria in SUMIF are not case-sensitive: East and EAST match the same text. However, spaces and other characters still matter. A cell containing East followed by a space may not match a criterion containing only East. Likewise, East Region does not match East. Data copied from a website or exported system can contain nonbreaking spaces or other invisible characters that are hard to spot.

Click a suspected cell and inspect its contents in the formula bar. For ordinary extra spaces, a helper column with TRIM can clean the text; TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words. It does not remove every kind of invisible character. For imported data, Excel’s CLEAN function or a careful find-and-replace may help, but check the cleaned results before replacing the original data.

Criteria operators need to be included as text. With sales in B2:B6, the formula =SUMIF(B2:B6,">=100",B2:B6) returns 370 for the illustrative values 120, 80, 150, 50, and 100. If the threshold is in D2, where D2 contains 100, use =SUMIF(B2:B6,">="&D2,B2:B6). The ampersand joins the operator to the cell value. Without that join, Excel may interpret the criterion differently than intended.

SUMIF also supports wildcard criteria. An asterisk represents any number of characters; a question mark represents one character. If A2:A20 contains product descriptions and B2:B20 contains amounts, =SUMIF(A2:A20,"*east*",B2:B20) adds amounts for descriptions containing east anywhere in the text. Wildcards are useful for partial matches, but they can accidentally include more records than intended. A tilde lets you search for a literal wildcard character, as in "~*" to find an asterisk.

Make sure the values are real numbers

A matching row will not contribute a useful amount if its sum-range cell is blank or contains text instead of a numeric value. Numbers imported as text can look exactly like numbers, yet behave differently in calculations. Common clues include a green triangle in the cell, values aligned differently from neighboring numbers, or numbers that do not respond to number formatting as expected.

Check a few affected cells rather than assuming every entry has the same type. You can use Excel’s warning menu to convert text numbers when it appears, or select a clean set of cells and use Data > Text to Columns, then complete the wizard without changing the delimiter settings. The precise choice depends on the imported content, so check the result. Avoid converting a column blindly if it also contains identifiers such as product codes with leading zeros; those may need to remain text.

Also confirm that the sum range is the amount column, not a neighboring text column. SUMIF does not add words or interpret currency symbols typed into text as numeric values. Apply a currency number format to actual numbers instead of entering symbols as part of the cell text. If the source data uses formulas, check whether those formulas return numbers or text strings that merely look numeric.

Handle dates and changing criteria carefully

Excel stores genuine dates as serial numbers and displays them using a date format. A date typed as text may look identical on screen but fail a date comparison. Check that the source dates are real Excel dates and that the criterion cell is also a real date. Formatting a text value as a date does not necessarily convert it into a date value.

For a date period, two conditions are usually clearer than trying to match a displayed month name. SUMIFS is a useful companion because it supports multiple criteria. If transaction dates are in A2:A100, amounts are in C2:C100, and E1 contains the first day of the month to report, =SUMIFS(C2:C100,A2:A100,">="&E1,A2:A100,"<"&EDATE(E1,1)) sums dates from the start of that month up to, but not including, the first day of the next month. For a real date of 1 March in E1, it includes March transactions, including times recorded on 31 March.

For a single threshold date, SUMIF can still be appropriate. With dates in A2:A100, amounts in C2:C100, and a cutoff date in E1, =SUMIF(A2:A100,">="&E1,C2:C100) adds amounts on or after the cutoff. If the result is unexpectedly zero, check whether the date cells contain times, text, or dates outside the intended range.

Align the ranges and inspect the references

A common formula error is using a criteria range and a sum range that are offset by one or more rows. If names are in A2:A20 and values are in B2:B20, the formula should generally use those same row boundaries. A formula using A2:A20 with B3:B21 can pair a person’s name with the next row’s amount, producing a plausible but incorrect answer.

In Excel, select the formula cell and read the colored outlines around referenced ranges, or click in the formula bar to inspect each reference. If you copy the formula down, consider whether references should move. Relative references such as A2:A20 adjust when copied; absolute references such as $A$2:$A$20 stay fixed. Press F4 while editing a selected reference on Windows to cycle through reference styles. On Mac, the equivalent key combination can depend on the keyboard and function-key settings; use the formula bar to enter dollar signs directly if needed.

For a growing table, converting the source data into an Excel Table can make formulas easier to maintain because table columns expand as rows are added. Select the data and choose Insert > Table, confirming whether the table has headers. Structured references can then refer to named columns, but verify that the criteria and amount columns still represent the same records.

Check calculation settings and formula entry

If the source values change but the SUMIF result does not, the workbook may use manual calculation. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. On Mac, use Formulas > Calculation Options > Automatic; names or placement can vary slightly by release. You can also recalculate a worksheet with Shift+F9 on Windows. On Mac, shortcut behavior may depend on the keyboard’s function-key settings, so the Formulas ribbon is a reliable route.

Check that the formula cell is not formatted as Text. If it is, Excel may display the formula characters instead of calculating them. Change the cell format to General, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter on Windows. On Mac, use the formula bar or the equivalent edit-and-confirm keys for your keyboard. Also look for a leading apostrophe before the equals sign, which forces an entry to be treated as text.

In some regional settings, Excel uses semicolons rather than commas between formula arguments. The examples here use commas as required for standard English-language Excel syntax; if Excel rejects a formula with a separator error, check your system’s list-separator setting or use the separator Excel inserts when you build the formula. Do not change commas inside quoted text criteria.

When SUMIF is not the right tool

SUMIF handles one condition. If the total must satisfy two or more conditions—for example, region equals East and product equals Chair—use SUMIFS. Its sum range comes first, followed by pairs of criteria ranges and criteria. Use a PivotTable when you want to summarize many categories interactively, compare totals by more than one field, or explore a large dataset without writing a separate formula for every category.

Another common misconception is that SUMIF automatically totals only the rows currently visible after filtering. It does not; it evaluates the specified ranges and criteria whether matching rows are hidden by a filter or not. If the requirement is specifically to total visible cells, choose an approach designed for filtered data, such as SUBTOTAL for appropriate simple cases, or use a helper column that explicitly marks the rows to include. Verify the method against your workbook’s layout.

Frequently Asked Questions

Why does SUMIF return zero when I can see matching text?
Check for trailing or nonstandard spaces, spelling differences, and whether the formula points to the intended criteria range. Also confirm that the corresponding sum cells contain numeric values. A visible match does not guarantee that the underlying cell contents are identical.

Does SUMIF distinguish between uppercase and lowercase?
No. SUMIF text criteria are case-insensitive, so East and EAST match. If capitalization must distinguish records, SUMIF alone is not sufficient; use a case-sensitive approach built with functions such as SUMPRODUCT and EXACT.

Why is SUMIF adding the wrong amounts?
Look for mismatched or shifted ranges, incorrect row boundaries, and wildcard criteria that include extra descriptions. Compare a few matching records manually. A formula can calculate successfully and still use the wrong columns or pair a criterion row with another row’s amount.

Can SUMIF use a criterion stored in another cell?
Yes. Put the criterion in a cell and use that cell as the criteria argument, as in =SUMIF(A2:A6,D2,B2:B6). For an operator and a threshold in D2, join them with an ampersand, such as ">="&D2.

Can SUMIF total only filtered or visible rows?
No. Filtering rows does not change what SUMIF includes. Use a method intended for visible-cell totals or add a helper criterion that identifies the rows to include, then check the result against a small sample.

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.