Excel SUMIF Not Working? 9 Fixes That Actually Work

Excel SUMIF not working turns a simple total into a bad result, a zero, a #VALUE! error, or a formula sitting there as text. Most failures come from formula order, criteria formatting, dirty data, calculation settings, or external workbook links.

Start with the formula itself, then move through the data and Excel settings that control whether the result calculates.

1. Fix the SUMIF formula order

SUMIF has one exact structure: =SUMIF(range, criteria, [sum_range]).

1. 1.Select the SUMIF cell.
2. 2.Click inside the formula bar.
3. 3.Check that the first argument is the range Excel tests.
4. 4.Check that the second argument is the condition.
5. 5.Check that the third argument is the range Excel adds.

Put the cells Excel tests in range, put the test in criteria, and put the cells Excel adds in [sum_range]. If you leave out [sum_range], Excel sums the cells in range. Use that only when the tested cells are also the cells you want to add.

2. Match the ranges before changing anything else

• Select the formula cell.
• Compare range and sum_range.
• Edit them so both cover the same number of rows or columns.
• Press Enter.

Matching range sizes are the safest practice for predictable results. This works: =SUMIF(E5:E16,C18,C5:C16). If the sizes differ, SUMIF still calculates: it starts at the first cell of sum_range and resizes it to the same dimensions as range, so a mismatch can quietly total cells you didn't intend. SUMIFS is stricter, where every criteria range must match sum_range.

When the total still looks wrong, use Formulas > Trace Precedents to see the cells feeding the formula.

3. Switch to SUMIFS for multiple conditions

SUMIF handles one condition. For two or more tests, use SUMIFS instead: =SUMIFS(sum_range, criteria_range1, criteria1, additional criteria ranges and criteria). The argument order changes with SUMIFS, so check it carefully before pressing Enter.

1. 1.Replace SUMIF with SUMIFS.
2. 2.Move the cells to add into the first argument.
3. 3.Add each criteria range and criteria as a pair.
4. 4.Make every criteria range the same size as sum_range.

4. Write the criteria the way Excel reads it

Text criteria and criteria with math or logical symbols need double quotation marks. Numeric criteria do not.

• Use =SUMIF(B2:B25,">5") for an operator test.
• Use =SUMIF(B2:B5,"John",C2:C5) for a text match.
• Use ? to match one character.
• Use * to match any sequence of characters.
• Use ~? or ~* to match an actual question mark or asterisk.

For criteria longer than 255 characters, shorten the string or split it with & or CONCAT, such as =SUMIF(B2:B12,"long string"&"another long string"). CONCATENATE still works but is kept only for backward compatibility, so Microsoft recommends CONCAT going forward.

5. Convert text numbers and text dates

1. 1.Select the cells that should be numbers.
2. 2.Select the error indicator in the top-left corner.
3. 3.Choose Convert to Number.

SUMIF totals break when values look like numbers or dates but are stored as text. You can also select the cells, press Alt+Shift+F10, and choose Convert to Number. If the warning is missing, turn it on from File > Options > Formulas > Enable background error checking.

For text dates, enter =DATEVALUE(cell_reference) in a helper column, fill it down, copy the results, then use Home > the arrow below Paste > Paste Special > Values. For two-digit-year text dates, go to File > Options > Formulas, turn on Enable background error checking, and under Error checking rules select Cells containing years represented as 2 digits. Then select the cell error button and choose Convert XX to 20XX or Convert XX to 19XX.

6. Remove spaces and hidden characters

A category that looks identical can fail a SUMIF match when it contains extra spaces or nonprinting characters. Clean the criteria column before rewriting the formula.

1. 1.Insert a helper column next to the messy data.
2. 2.Enter =TRIM(A2) to remove extra 7-bit ASCII spaces.
3. 3.Use =CLEAN(A2) for the first 32 nonprinting ASCII characters.
4. 4.Wrap both with SUBSTITUTE for higher Unicode characters they miss, such as the nonbreaking space: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))).
5. 5.Fill the formula down.
6. 6.Copy the helper column.
7. 7.Use Home > Paste > Paste Special > Values over the original column.

For repeated bad text, press Ctrl+H, enter the old text in Find what, enter the corrected text in Replace with, then replace the entries.

7. Turn calculation back on

• In Excel for Windows, go to File > Options > Formulas > Workbook Calculation > Automatic.
• From the ribbon, use Formulas > Calculation Options > Automatic.
• In Excel for the web, use Formulas > Calculation Options > Automatic.

If the formula is right but the total refuses to update, these settings return Excel to automatic calculation.

To force a refresh, use Formulas > Calculate Now for all open worksheets, or Formulas > Calculate Sheet for the active sheet. You can also press F9, Shift+F9, Ctrl+Alt+F9, or Ctrl+Shift+Alt+F9 for the documented recalculation commands.

8. Show the result instead of the formula

If Excel displays =SUMIF(range, criteria, [sum_range]) instead of the answer, the worksheet is showing formulas or the cell is formatted as text.

1. 1.Go to Formulas > Show Formulas to turn formula display off.
2. 2.Or press Ctrl + `.
3. 3.Right-click the SUMIF cell and choose Format Cells.
4. 4.Select General.
5. 5.Press F2, then press Enter.

For a larger text-formatted range, apply the correct number format, then use Data > Text to Columns > Finish.

9. Open linked workbooks and fix app-level issues

SUMIF returns #VALUE! when it calculates against a closed external workbook. Open the source workbook named in the formula, then press F9. If the source workbook has to remain closed, replace SUMIF with Microsoft’s documented SUM(IF()) array formula workaround.

If the file opens in Protected View, select Enable Editing only when you trust the file. In a shared web workbook, use the mode menu and switch from Viewing to Editing when you have permission.

When Excel freezes or behaves badly outside the SUMIF cell, update the app. On Windows, open any Microsoft 365 app and use File > Account > Update Options > Update Now. On Mac, open Excel and use Help > Check for Updates.

Frequently Asked Questions

Why does SUMIF work in one file but not another?

Check calculation mode, external workbook links, protected view, and whether the formula ranges point to the intended cells. Use Formulas > Trace Precedents to verify the referenced ranges.

What is the correct SUMIF syntax?

The syntax is =SUMIF(range, criteria, [sum_range]). Range is tested, criteria defines the match, and sum_range is added.

How do I sum by category without writing a formula?

If your Microsoft 365 subscription includes Analyze Data, select a cell in the data range, then use Home > Analyze Data. Ask for the sum by category and insert the returned table, chart, or PivotTable if it matches the result you need. Natural-language queries roll out gradually and may not be available in every region, so if Analyze Data is missing, use a PivotTable or a SUMIF/SUMIFS formula instead.

Can Copilot create a SUMIF formula in Excel?

Yes, when Copilot in Excel is included with your Microsoft 365 license and enabled by your organization. Select the Copilot icon in Excel and ask for the formula using your column names or ranges, such as Create a SUMIF formula that sums Amount when Category equals Utilities. If the Copilot icon doesn't appear, it isn't part of your current plan or has been turned off by an admin.