Why SUMIFS Returns 0: 6 Causes and How to Fix Each

Last updated: 3 October 2026 · Download: sumifs-zero-test-workbook.xlsx, with one sheet per cause, a broken formula and a fixed formula on each.

When SUMIFS returns 0, or a total that's clearly too small, Excel almost never shows an error to help you. The formula is valid. It's simply adding up zero matching rows, or skipping some of the values in the rows that match.

This guide covers the six situations behind most of these silent zeros, with a test for each and the fixed formula. Every example uses the same eight rows of sales data, so you can compare the broken and fixed results directly in the downloadable workbook:

The correct answers are: West = 380, East = 210, West + East = 590, and everything dated on or after 1 February 2026 = 450. In the workbook the data sits in H6:J13: Region in column H, Date in column I, Amount in column J.

First: is it the criteria or the sum range?

Before you change anything, run a COUNTIFS with exactly the same criteria as your SUMIFS:

=COUNTIFS(H6:H13,"West")

• COUNTIFS returns 0: no rows match your criteria. Look at causes 2, 3, 4 and 6.
• COUNTIFS returns the number of rows you expected, but SUMIFS is still too low: the rows match, but their amounts aren't being added. Look at causes 1 and 5.

This one check halves the search.

Cause 1: the amounts are stored as text

Sheet "1 Text numbers" in the workbook.

SUMIFS only adds real numbers. If some values in the sum range are text, which is common after pasting from a website, an email or a CSV export, it skips them without any warning. In the workbook, two of the three West amounts are stored as text:

=SUMIFS(J6:J13,H6:H13,"West")   → 60   (should be 380)

Test: =COUNTIFS(H6:H13,"West") returns 3, so the rows match. =COUNT(J6:J13) returns 6, not 8, so two amounts aren't numbers. Text numbers also align to the left of the cell by default and often show a small green triangle.

Fix: convert the column to real numbers. Select it and use Data › Text to Columns › Finish, or type 1 in an empty cell, copy it, select the amounts, and use Paste Special › Multiply. If you can't change the data, SUMPRODUCT can convert as it adds:

=SUMPRODUCT((H6:H13="West")*VALUE(J6:J13))   → 380

Cause 2: extra spaces in the criteria column

Sheet "2 Hidden spaces".

A criterion like "West" has to match the whole cell. If the data contains West with a trailing space, that row doesn't count. Nothing on screen shows the difference.

=SUMIFS(J6:J13,H6:H13,"West")   → 60   (two West rows have a trailing space)

Test: =COUNTIFS(H6:H13,"West") returns 1 instead of 3. =LEN(H6) returns 5, although "West" has only 4 letters.

Fix: clean the column. Add a helper column with =TRIM(H6), fill it down, then copy it and Paste Special › Values over the original. As a quick workaround, a wildcard matches anything that starts with West:

=SUMIFS(J6:J13,H6:H13,"West*")   → 380

Be careful with wildcards: "West*" would also match "Westfield". To match exactly while ignoring the spaces without editing the data, use:

=SUMPRODUCT((TRIM(H6:H13)="West")*J6:J13)   → 380

If TRIM doesn't help, the space may be a non-breaking space (character 160) from web data. The VLOOKUP #N/A guide shows how to find and remove it. The same fix applies here.

Cause 3: the cell reference is inside the quotes

Sheet "3 Quoted ref".

This is the most common cause of a SUMIFS that returns exactly 0 with dates or numbers. The goal is to add everything on or after the date in K6, but the formula is written like this:

=SUMIFS(J6:J13,I6:I13,">=K6")   → 0

Everything inside the quotes is plain text. Excel compares each date with the literal letters "K6", nothing matches, and the result is 0. The operator must be in quotes and joined to the cell reference with &:

=SUMIFS(J6:J13,I6:I13,">="&K6)   → 450

The same rule applies to typed dates. A criterion like ">=1/2/2026" is read according to your regional settings, so it means 1 February in some countries and 2 January in others. Build the date with the DATE function instead:

=SUMIFS(J6:J13,I6:I13,">="&DATE(2026,2,1))   → 450

Test: click the formula and look at the criterion. If a cell address appears inside the quotation marks, this is the cause.

Cause 4: the dates are stored as text

Sheet "4 Text dates".

Sometimes the formula is correct and the data is the problem. A column can look like dates while holding text such as 2026-02-02, which happens often with CSV exports and system reports. Excel stores real dates as numbers. A criterion like ">="&DATE(2026,2,1) is a number comparison, and SUMIFS doesn't treat text as greater than or equal to a number, so nothing matches:

=SUMIFS(J6:J13,I6:I13,">="&DATE(2026,2,1))   → 0

Test: =COUNT(I6:I13) counts real numbers, including real dates. Here it returns 0 of 8. You can also select a date cell and change its format to General. A real date turns into a number (2026-02-02 becomes 46055), while text stays the same.

Fix: convert the column into real dates. Select it, choose Data › Text to Columns, click Next twice, choose Date with the order that matches your data (YMD for 2026-02-02), and click Finish. If you need to leave the data as it is, convert inside the formula:

=SUMPRODUCT((DATEVALUE(I6:I13)>=DATE(2026,2,1))*J6:J13)   → 450

Cause 5: the ranges start on different rows

Sheet "5 Shifted ranges".

SUMIFS pairs each row of the criteria range with the row in the same position in the sum range. If the ranges are the same size but don't start on the same row, Excel shows no error. It just adds the wrong rows:

=SUMIFS(J7:J14,H6:H13,"West")   → 340   (should be 380)

Each West row picks up the amount from the row *below* it: 80 + 150 + 110. With different data the result can just as easily be 0. This usually happens after inserting or deleting rows, or when one range is typed by hand and the other is selected with the mouse.

Test: compare the row numbers in every range of the formula. All ranges should start and end on the same rows: J6:J13 and H6:H13, not J7:J14. If the ranges are different *sizes*, for example J6:J13 and H6:H20, SUMIFS returns #VALUE! instead.

Fix: correct the ranges. The way to make this mistake impossible is to convert the data into an Excel Table (Ctrl+T) and use column names:

=SUMIFS(tblSales[Amount],tblSales[Region],"West")

Table columns always line up, and they grow when you add rows.

Cause 6: two criteria on the same column

Sheet "6 OR on one col".

Every criteria pair in SUMIFS must be true at the same time. That's AND logic. So this formula, meant to total West *or* East, asks for rows where the region is West and East simultaneously:

=SUMIFS(J6:J13,H6:H13,"West",H6:H13,"East")   → 0

No row can be both, so the answer is always 0.

Fix: add two SUMIFS together:

=SUMIFS(J6:J13,H6:H13,"West")+SUMIFS(J6:J13,H6:H13,"East")   → 590

For more than two values, pass them as an array constant and add up the results:

=SUMPRODUCT(SUMIFS(J6:J13,H6:H13,{"West","East"}))   → 590

In Microsoft 365, SUM works in place of SUMPRODUCT here. In older versions, SUMPRODUCT avoids having to enter the formula as an array formula.

Two criteria on the same column are fine when they describe a range, such as dates between two values: ">="&DATE(2026,2,1) and "<"&DATE(2026,3,1) on the date column. Both can be true at once, so AND logic is exactly what you want.

Checklist

When SUMIFS returns 0 or too little:

1. Run COUNTIFS with the same criteria. Zero matches means the criteria are the problem; a correct count means the sum range is.
2. Is a cell reference inside the quotes? Use ">="&K6, not ">=K6" (cause 3).
3. Does COUNT on the sum range or date column return fewer than the number of rows? The values are stored as text (causes 1 and 4).
4. Does LEN show extra characters in the criteria column? Clean it with TRIM (cause 2).
5. Do all ranges start and end on the same rows? (cause 5)
6. Are two criteria on the same column meant as OR? Add two SUMIFS instead (cause 6).

To read a long SUMIFS someone else wrote, paste it into the Formula Explainer to see each criteria pair listed separately. If a total comes back as an error code instead of 0, the Excel Error Decoder explains what each one means. If you're building a summary of many totals by region or month, the Pivot Table Planner may be a faster route than a grid of SUMIFS.

FAQ

Why does SUMIFS return 0 when I can see matching rows? Either the criteria don't match the cells exactly, or the amounts in those rows are stored as text. A COUNTIFS with the same criteria tells you which: a count of 0 points to the criteria, and a correct count points to the amounts.

Is SUMIFS case-sensitive? No. "west" and "West" match the same cells. Case is never the reason SUMIFS returns 0.

What's the difference between SUMIF and SUMIFS? SUMIF takes one condition, and its sum range comes last: SUMIF(range, criteria, sum_range). SUMIFS takes one or more conditions, and its sum range comes first: SUMIFS(sum_range, range1, criteria1, ...). Swapping the order when changing from one to the other is a common source of wrong totals.

How do I sum values that are not blank, or are blank? Use "<>" as the criterion for non-blank cells, for example =SUMIFS(J6:J13,H6:H13,"<>"), and "" for blank cells.

Does the test workbook work in Google Sheets? Yes. Upload it to Google Drive and open it with Google Sheets. All of the formulas in it are supported.