Excel guide · 5 min read

SUMIFS not working or returning 0: how to fix it

Q: Can SUMIFS use OR instead of AND?

Not directly. Wrap it in SUM with an array constant, like =SUM(SUMIFS(D2:D9,C2:C9,{"Lena","Priya"})), or add two SUMIFS together. Only do that for values in the same column, so no row is counted twice.

Quick answerSUMIFS usually returns 0 or a too-small total because a value in the data is not what it looks like: numbers or dates stored as text, or a criteria cell with a hidden space. It returns #VALUE! when the sum range and criteria ranges are different sizes. Also check the syntax: operators go in quotes and join to a cell with &, as in ">="&G1, and sum_range comes first.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	F	G
1	Date	Region	Rep	Amount	From	Oct 1, 2026
2	Sep 28, 2026	East	Lena	1200	To	Oct 31, 2026
3	Oct 2, 2026	East	Omar	850	Region	East
4	Oct 7, 2026	West	Lena	640
5	Oct 12, 2026	East	Priya	975
6	Oct 15, 2026	East	Omar	430
7	10/21/2026	East	Lena	1100
8	Oct 26, 2026	West	Priya	720
9	Nov 2, 2026	East	Omar	560

Download this sheet as an Excel practice file (.xlsx)

1. sum_range goes first (the opposite of SUMIF)

SUMIF is =SUMIF(range, criteria, sum_range). SUMIFS flips it: =SUMIFS(sum_range, criteria_range1, criteria1, ...). People who learned SUMIF first type the ranges in the old order, and SUMIFS then sums the wrong column or shows an error.

The sheet is an October sales log. This is the correct shape for "total sales for East". Both functions return 3710 here, but keep reading: that total is already wrong, because three rows are being skipped silently.

=SUMIFS(D2:D9,B2:B9,"East")

Result: 3710

3710. The real East total is higher; the next sections show why rows 5, 6 and 7 are missing.

2. #VALUE!: the ranges are different sizes

Every criteria range in SUMIFS must be exactly the same size and shape as sum_range. Here the region range stops at row 8 while the amounts go to row 9, so SUMIFS refuses with #VALUE!.

This trips people up because SUMIF is forgiving: =SUMIF(B2:B9,"East",D2:D8) quietly stretches the sum range to match and returns 3710. Converting a SUMIF to SUMIFS exposes the mismatch. Check that every range starts and ends on the same rows, or use whole columns (D:D, B:B) or an Excel table so they always match.

=SUMIFS(D2:D9,B2:B8,"East")

Result: #VALUE!

#VALUE!, because B2:B8 has 7 cells and D2:D9 has 8.

3. Comparing to a cell: ">="&G1, not ">=G1"

To total East sales between the dates in G1 and G2, the operator goes in quotes and the cell is joined on with &. Written as ">=G1", Excel looks for values greater than or equal to the text "G1", finds none, and returns 0 with no error.

This is the most common cause of a SUMIFS date range returning 0. The same rule applies to "<>"&G3 and ">"&100.

=SUMIFS(D2:D9,B2:B9,G3,A2:A9,">="&G1,A2:A9,"<="&G2)

Result: 850

850. The syntax is right now, but only one October East sale is counted. The other three have data problems.

4. A hidden space in the criteria column

B5 looks like East but is "East " with a trailing space, a classic leftover from copy-paste and CSV exports. SUMIFS needs an exact match (it ignores upper and lower case, but not spaces), so Priya's 975 is skipped.

Spot it with =LEN(B5), which returns 5 instead of 4. The proper fix is to clean the column: put =TRIM(B2) in a spare column, paste as values over B, or use Find and Replace. A quick workaround is a wildcard, "East*", but it also matches "Eastern" or "East Coast", so only use it when that is acceptable.

=SUMIFS(D2:D9,B2:B9,"East*",A2:A9,">="&G1,A2:A9,"<="&G2)

Result: 1825

1825, now including the 975 from row 5.

5. Numbers stored as text in the sum range

D6 holds 430 as text. SUMIFS, like SUM, ignores text in the range it adds up, so the row matches every condition and still adds nothing. Text numbers are usually left-aligned and may show a small green triangle.

Count them: COUNTA counts every filled cell and COUNT counts only real numbers, so the difference is the number of text values. Fix the column by selecting it and choosing Data > Text to Columns > Finish, or by clicking the warning icon and choosing Convert to Number.

=COUNTA(D2:D9)-COUNT(D2:D9)

Result: 1

1, so one amount in D2:D9 is text and SUMIFS is skipping it.

6. Dates stored as text

A7 shows 10/21/2026 but it is text, so it is neither ">=" nor "<=" a real date and the 1100 on that row never matches the date conditions. Text dates come in from CSV files, web forms, and systems that use a different date order than your PC.

The same COUNTA minus COUNT check works on the date column. Convert with Data > Text to Columns and pick the right date order (MDY or DMY) on step 3, or use =DATEVALUE(A7) in a helper column. Once B5, D6 and A7 are fixed, the original SUMIFS from step 3 includes all four October East rows with no wildcard needed.

=ISNUMBER(A7)

Result: FALSE

FALSE. A real date would return TRUE, because Excel stores dates as numbers.

7. Two values in the same column (OR logic)

SUMIFS joins every condition with AND. =SUMIFS(D2:D9,C2:C9,"Lena",C2:C9,"Priya") asks for rows where the rep is Lena and also Priya at the same time, which never happens, so it returns 0.

For "Lena or Priya", give SUMIFS an array constant of both names. It returns one total per name, and SUM adds them. This works in any version of Excel without Ctrl+Shift+Enter. For a longer list, put the names in cells and use SUMPRODUCT(SUMIFS(D2:D9,C2:C9,J2:J5)).

=SUM(SUMIFS(D2:D9,C2:C9,{"Lena","Priya"}))

Result: 4635

4635: Lena's 2940 plus Priya's 1695.

Quick checklist

If SUMIFS returns #VALUE!, compare the range sizes first. If it returns 0 or too little, check in this order: is sum_range first, are operators written as ">="&cell, do LEN checks reveal extra spaces, and does COUNTA minus COUNT show text numbers or text dates.

One less obvious cause: a workbook set to manual calculation shows old results until you press F9 or set Formulas > Calculation Options > Automatic.

Common mistakes

Writing ">=G1" or ">="G1 in the criteria

Put the operator in quotes and join the cell with &: ">="&G1. The quoted version compares to the text G1 and returns 0.

Converting SUMIF to SUMIFS without reordering

SUMIFS takes sum_range first: =SUMIFS(D2:D9,B2:B9,"East"), not =SUMIFS(B2:B9,"East",D2:D9).

Ranges that start or end on different rows

SUMIFS returns #VALUE! unless every range is the same size. Use matching ranges, whole columns or a table.

Repeating the same column for OR conditions

Two conditions on one column must both be true, which usually returns 0. Use =SUM(SUMIFS(...,{"A","B"})) instead.

FAQ

Why is my SUMIFS returning 0?

Usually because the matches are not exact: a trailing space in the data, numbers or dates stored as text, or a criteria like ">=G1" instead of ">="&G1. Check one matching row with =LEN() and =ISNUMBER() to see which it is.

Why does SUMIFS give #VALUE!?

The sum range and the criteria ranges are not the same size, for example D2:D9 with B2:B8. SUMIFS needs every range to have the same number of rows and columns.

Is SUMIFS case-sensitive?

No. "east", "East" and "EAST" all match. Spaces do count, though, so "East " with a trailing space does not match "East".

Why does my SUMIFS with dates not work?

Either the dates in the data are text, or the date criteria is written wrong. Use ">="&G1 with a real date in G1, or ">="&DATE(2026,10,1) to type it into the formula. Test the date column with ISNUMBER.

Can SUMIFS use OR instead of AND?

Not directly. Wrap it in SUM with an array constant, like =SUM(SUMIFS(D2:D9,C2:C9,{"Lena","Priya"})), or add two SUMIFS together. Only do that for values in the same column, so no row is counted twice.

Functions used

Related guides

Updated November 3, 2026