SUMIFS is one of the most useful Excel functions and one of the easiest to break. A single mismatched range, a stray space in a criterion or a date stored as text can turn a correct-looking formula into 0.
Quick answer: check four things before rewriting the formula: every range must be the same size, the sum range comes first, text criteria longer than 255 characters or containing operators need care, and the values you are matching must share a data type. Then test with one criterion at a time.
The Syntax, in the Right Order
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The sum range comes first. This is the opposite of SUMIF, where the criteria range comes first. Mixing the two is a common source of a wrong total that still looks plausible.
Example: total sales for the North region in Q1:
=SUMIFS(Sales[Amount],Sales[Region],"North",Sales[Quarter],"Q1")
1. The Ranges Are Different Sizes
Every criteria range must match the sum range in rows and columns. Excel does not always warn you; it may calculate a total from the overlapping rows and silently ignore the rest. This is the most common cause of a total that is close but wrong.
Use structured references from an Excel Table so every column stays the same length. If you use A1 ranges, select the whole column with Ctrl + Shift + Down and verify the last row is the same in each range.
2. The Criteria Are Text, Not Cell References
SUMIFS criteria are strings, even when they look like numbers. These behave differently:
"North"— matches the text North exactly (case-insensitive).H2— matches the value in H2; use this for a cell-driven dashboard.">"&H2— builds a comparison from a cell value.">1000"— a fixed numeric comparison."North*"— wildcard; matches North, Northern, North-East.
A missing & is a classic error: ">H2" compares against the literal text H2, not the value in the cell.
3. Numbers Are Stored as Text
SUMIFS sums numeric cells in the sum range. If the amounts are text, the criteria can match but the total is zero or too low. Test with =ISNUMBER and =COUNT, then convert the column with Data → Text to Columns → Finish.
The same problem affects criteria. A region code stored as text "01" will not match numeric 1. Standardise both sides before building the formula. The numbers stored as text guide has the full conversion sequence.
4. Dates and Times Do Not Match
Date criteria are the second most common failure. ">="&DATE(2026,1,1) is reliable; ">=01/01/2026" depends on your regional settings and may be read as text.
- Use
DATE()or a cell reference for the boundary. - Include the time component when the source has timestamps: a row at 01/01/2026 09:00 is greater than 01/01/2026 00:00.
- For a whole month, use
">="&startand"<="&EOMONTH(start,0), or"<"&EOMONTH(start,0)+1. - If the source dates are text, convert them first with the date fix guide.
5. The Sum Range Is the Wrong Column
It is easy to select the quantity column when you meant the revenue column, or to include the header row. Check the first result manually: filter the source to one matching row and compare the SUMIFS result with the visible amount. If they differ, the range is wrong.
Also check for hidden columns inside the sum range. SUMIFS includes hidden rows and columns; it does not behave like SUBTOTAL. If the rubric expects visible rows only, use SUBTOTAL with FILTER or a helper column instead.
6. The Criteria Do Not Match Because of Spaces
"North" does not match "North ". Imported data often carries trailing spaces, non-breaking spaces or inconsistent capitalisation. Clean the source column with =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))), then paste the values back.
For a quick check, use =COUNTIF(Region,"North") and compare it with the number of rows you can see. A lower count means hidden characters or a different label.
7. You Need OR Logic, Not AND Logic
SUMIFS joins criteria with AND. Adding Region = North and Region = South in the same formula returns zero, because no row can be both. For OR logic, add two SUMIFS results:
=SUMIFS(Amount,Region,"North")+SUMIFS(Amount,Region,"South")
For three or more alternatives, use SUMPRODUCT with ISNUMBER(MATCH(...)), or add a helper column that labels the group and use one SUMIFS on that column. The helper column is easier for a marker to audit.
8. The Formula Is Not Calculating
If SUMIFS shows as text or returns the same value after the source changes, the problem is calculation, not criteria. Check Formulas → Calculation Options → Automatic, the cell format, and whether Show Formulas is on. The formula not calculating guide walks through the ten causes.
9. The Criteria Range Includes the Wrong Sheet
A dashboard formula often points at a sheet that was copied and renamed. The formula may still reference the old sheet name, or a closed workbook. Check the formula bar for [Book1] or a sheet name that no longer exists. Use Formulas → Trace Precedents to see what the range actually covers.
For repeatable dashboards, keep the data and the calculations on separate sheets and use Table names. Renaming a sheet then does not break the formula.
A Debugging Sequence That Finds the Cause Fast
- Copy the formula and remove every criterion except one.
- Replace each range with a fixed range and confirm the total.
- Test the criterion on its own with COUNTIF.
- Check the sum range with COUNT and ISNUMBER.
- Add the criteria back one at a time.
- Compare the final total with a filtered subtotal.
Before You Submit
Test a normal case, a blank criterion, a date boundary and a value that should return zero. Confirm that your totals reconcile to the source with a pivot or a filtered subtotal. For an assignment, add a short note explaining the criteria logic so the marker can follow it.
Related: COUNTIF and SUMIFS criteria guide, SUMIFS between two dates, and numbers stored as text. For a data-heavy assignment, see Excel data analysis assignment help.
