SUMIF Not Working? Common Causes and Fixes

When SUMIF is not working, it rarely shows an error. It returns 0, or a total that's quietly wrong. The usual causes are numbers stored as text, arguments in the wrong order, a missing sum range, spaces in the data, and ranges of different sizes. This guide shows how to spot and fix each one.

How SUMIF should work#

SUMIF adds the numbers in one column when a matching cell in another column meets a condition. The syntax has three parts:

=SUMIF(range, criteria, sum_range)

All the examples use this sales table in A1:C7:

=SUMIF(A2:A7,"East",C2:C7) should return 900 (500 + 250 + 150). If yours doesn't, work through the causes below. The full SUMIF guide covers the basics.

Cause 1: the numbers are stored as text#

SUMIF only adds real numbers. If some cells in the sum range hold numbers stored as text, SUMIF skips them, so the total is too low or 0. This is common with data from CSV files and other systems.

Text numbers usually sit on the left of the cell and show a small green triangle. Select them, click the warning icon and choose Convert to Number, or use Data > Text to Columns > Finish on the whole column.

If you can't change the data, convert inside the formula. This version uses VALUE to turn each sales cell into a number:

=SUMPRODUCT((A2:A7="East")*VALUE(C2:C7))

It returns 900 even when the sales are stored as text. The VALUE function guide explains what VALUE can convert.

Cause 2: the arguments are in the wrong order#

SUMIF and SUMIFS put the sum range in different places, and mixing them up is very common:

=SUMIF(A2:A7,"East",C2:C7)
=SUMIFS(C2:C7,A2:A7,"East")

Both return 900. SUMIF puts the cells to add last. SUMIFS puts them first, because it can take many conditions after that. If you write =SUMIF(C2:C7,"East",A2:A7), SUMIF looks for "East" in the sales numbers, finds nothing, and returns 0.

Many people use SUMIFS for everything so they only learn one order. See SUMIF vs SUMIFS for when each makes sense.

Cause 3: the sum range is missing#

The sum range is optional. If you leave it out, SUMIF adds the cells in the range itself. That's useful for something like =SUMIF(C2:C7,">=300"), which adds sales of 300 or more: 1,200 (500 + 300 + 400).

But =SUMIF(A2:A7,"East") adds up the Region column, which holds text, so the result is 0. Add the sum range as the third argument and it works.

Cause 4: spaces and other mismatches in the criteria#

SUMIF matches the criteria to whole cells, ignoring only capital letters. "East " with a trailing space doesn't match "East". Check a suspicious cell with =LEN(A2): "East" should give 4.

Fix it by cleaning the column with TRIM in a helper column, or match around the spaces with a wildcard such as "*East*". The same rules apply to COUNTIF, so the guide to COUNTIF not working is worth a read if both are giving you trouble.

Operators also need care:

• Operators go inside quotes: ">=300", not >=300.
• Not equal to is "<>West". The guide to SUMIF not equal to has more examples.
• For a value in a cell, keep the cell outside the quotes: ">="&E1. Writing ">=E1" compares with the letters E1 and returns 0.

Cause 5: the ranges are different sizes#

SUMIF doesn't give an error when range and sum_range are different sizes. Instead, it takes the top-left cell of sum_range and stretches it to match the size of range. That can add the wrong cells.

For example, =SUMIF(A2:A7,"West",C3:C8) starts the sum range one row too low. Each "West" row now adds the sales from the row below it: 250 from row 4 plus an empty C8. The result is 250 instead of the correct 500, and nothing warns you.

Always make both ranges start and end on the same rows. SUMIFS is stricter: it returns #VALUE! when the ranges don't match, which helps you catch the mistake.

Cause 6: date criteria that don't match#

To add values by date, put the operator in quotes and build the date with the DATE function. If column D held order dates, this adds the sales from 1 March 2024 onwards:

=SUMIF(D2:D7,">="&DATE(2024,3,1),C2:C7)

Typing the date inside the text, as in ">=01/03/2024", can go wrong because Excel reads it using your computer's date settings, so 01/03 may mean 3 January. DATE always takes year, month and day in that order. If the result is still 0, check that the dates are real dates: =ISNUMBER(D2) should return TRUE. Text dates sit on the left of the cell and need converting first.

Cause 7: ranges move when you copy the formula#

If you fill a SUMIF down a summary table, unlocked ranges shift with each row. In the next row, A2:A7 becomes A3:A8 and the first record drops out.

Lock the data ranges with $ signs and leave the criteria cell unlocked:

=SUMIF($A$2:$A$7,E2,$C$2:$C$7)

With "West" in E2, this returns 500 (300 + 200). Fill it down next to a list of regions and every row uses the full table. Press F4 after selecting a reference to add the $ signs (Mac: ⌘T). The SUMIF function guide shows more patterns.

If a lookup on the same data is also failing, the guide to INDEX MATCH not working covers similar problems with text, spaces and ranges.

Practise it#

Practise SUMIF in a live spreadsheet: you type each formula and it's checked instantly, including totals by region and by amount. Then practise turning text into numbers with VALUE, so imported data never breaks your totals again. Start with the exercise on this page.