SUMIF Syntax and How It Works
=SUMIF(criteria_range, criteria, sum_range)- criteria_range: The column Excel checks to decide whether to include a row.
- criteria: The value or condition to match. Can be a cell reference, text, number, or wildcard.
- sum_range: The column Excel adds values from when the criteria is matched. If omitted, Excel sums the criteria_range itself.
The criteria_range and sum_range must be the same size and aligned row-by-row. If they are different sizes, SUMIF may silently return a wrong total without any error message.
How to Quickly Test Whether SUMIF Is Working
Before debugging, verify that the criteria actually matches any rows. Use COUNTIF with the same criteria and range:
=COUNTIF(criteria_range, criteria)If COUNTIF returns 0, the criteria does not match any cells — the problem is in the match, not the sum. If COUNTIF returns a positive number but SUMIF returns 0, the sum range values are stored as text. If both return a value but SUMIF's total seems wrong, the range sizes may be mismatched or wildcard criteria are matching more rows than intended.
Problem 1: Numbers in the Sum Range Are Stored as Text
SUMIF adds numbers. If the sum_range cells look like numbers but are stored as text, SUMIF treats them as 0 and returns a sum of zero. The cell might display 500, but SUMIF sees nothing summable.
How to identify: check for a small green triangle in the top-left corner of sum range cells, or use =ISNUMBER(B2) — if it returns FALSE for a cell that displays a number, the value is stored as text.
Fix: select the sum range column, go to Data > Text to Columns, click Finish without changing settings. Excel reinterprets the values as numbers. Alternatively, use a helper column: =VALUE(B2) to create numeric versions, then use the helper column as the sum_range.
Problem 2: Criteria Range and Sum Range Are Different Sizes
This is one of the most dangerous SUMIF mistakes because it produces a wrong answer, not an error.
=SUMIF(A2:A100,"East",B2:B50)Here the criteria range is 99 rows (A2:A100) but the sum range is only 49 rows (B2:B50). When the criteria matches a row above B50, SUMIF still sums — but it sums a cell from the wrong row, or a cell outside the intended range. The result is a believable but incorrect total.
Fix: always use matching ranges of identical size. Better: use full-column references to avoid size mismatches entirely:
=SUMIF(A:A,"East",B:B)Or use Excel Tables so ranges always expand together.
Problem 3: Extra Spaces in the Criteria Range
Extra spaces are invisible but break text matching. If the criteria range contains "East " (with a trailing space) and your criteria is "East" (no space), SUMIF does not find a match and returns 0.
How to check: use =LEN(A2) and compare it to =LEN(TRIM(A2)). If the lengths differ, the cell has extra spaces.
Fix: clean the criteria range with TRIM in a helper column before using it in SUMIF. Alternatively, use a wildcard to bypass spaces:
=SUMIF(A:A,"*East*",B:B)But be careful — see Problem 5 below about wildcards matching more than intended.
See the full guide on removing spaces in Excel for all cleanup methods.
Problem 4: Dates Are Compared as Text
Date criteria in SUMIF need to be real Excel date values, not text strings. If the criteria_range contains real dates but you provide a text string as the criteria, they will not match:
=SUMIF(A:A,"2026-06-01",B:B)This likely returns 0 because "2026-06-01" is a text string and the dates in column A are real Excel date values (serial numbers), not text.
Fix: use the DATE function for date criteria:
=SUMIF(A:A,DATE(2026,6,1),B:B)Or reference a cell that contains the date: =SUMIF(A:A,D2,B:B) where D2 is formatted as a date cell.
For date range comparisons (sum everything in June 2026), use SUMIFS:
=SUMIFS(B:B,A:A,">="&DATE(2026,6,1),A:A,"<="&DATE(2026,6,30))Problem 5: Wildcard Criteria Match Too Many Rows
Wildcards in SUMIF allow partial matching, but they can match more rows than intended:
*East*matches "East", "Northeast", "Eastern Region", "South East", and any other value containing the word East.East*matches "East", "Eastern", "East Region", but not "Northeast"."East"(exact, no wildcards) matches only the exact text "East".
If your SUMIF total is higher than expected, check your wildcard criteria. Use COUNTIF with the same criteria to count how many rows are matching and verify the count is correct.
Problem 6: The Sum Range Includes Numbers and Blanks
SUMIF skips blank cells in the sum range — they are treated as 0 and not added. This is usually correct behavior. But if a blank cell was expected to contain a value and was accidentally left empty, the sum will be lower than correct.
Use COUNTA on the sum range to count non-blank cells. Compare to COUNTIF on the criteria range to see if the expected number of rows are actually being summed:
=COUNTIF(A:A,"East")If this returns 10 but only 8 cells in column B have values, 2 cells are blank that should contain amounts.
Problem 7: Hidden or Filtered Rows Are Included
SUMIF does not skip hidden or filtered rows. It sums all matching cells in the criteria range, whether visible or not. If your data has active filters and you expect SUMIF to sum only visible rows, the result will include hidden rows and be higher than expected.
Fix: for sums that should only include visible rows, use SUBTOTAL or AGGREGATE instead of SUMIF. These functions have a mode to ignore hidden rows. You may need a helper column to first mark the criteria match, then use SUBTOTAL on the helper.
Problem 8: Criteria Is a Number but Stored as Text
If the criteria range contains real numbers like 100, 200, 300 and your criteria is "100" (a text string), SUMIF may not match correctly. SUMIF is flexible about some type mismatches, but not all.
Fix: make sure numeric criteria are numbers, not text. Instead of "100", use 100 (no quotes) or reference a cell containing the number.
The same applies in reverse: if the criteria range contains text like "100" but your criteria is the number 100, they may not match. Use VALUE to convert text numbers: =SUMIF(A:A,VALUE(D2),B:B).
SUMIF Troubleshooting Checklist
- Verify with COUNTIF that the criteria matches any rows at all.
- Check sum range values with ISNUMBER — text-formatted numbers return FALSE.
- Confirm criteria_range and sum_range are the same size.
- Check for extra spaces with LEN vs LEN(TRIM()).
- Use DATE() for date criteria, not text strings.
- Review wildcard criteria — do they match more rows than intended?
- Remember SUMIF includes hidden rows — use SUBTOTAL if you need visible-only sums.
- Make sure numeric criteria are numbers, not text strings in quotes.
When to Use SUMIFS Instead of SUMIF
SUMIFS is the multi-criteria version of SUMIF. The syntax is slightly different — the sum_range comes first:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)Use SUMIFS when you need to match on two or more conditions, such as region AND month, or product AND status. SUMIFS supports up to 127 criteria pairs. For single-criteria sums, either SUMIF or SUMIFS works.
SUMIFS also avoids a range-size problem that SUMIF has: in SUMIFS, all ranges must be the same size, and Excel enforces this with a #VALUE! error instead of silently returning wrong results.
Frequently Asked Questions
Does SUMIF work with cells that contain formula results?
Yes. SUMIF evaluates the value in each cell, whether that value comes from a formula or was typed directly. A cell showing 100 because of a formula is treated the same as a cell containing the number 100 typed directly, as long as the formula returns a real number (not text).
Why does SUMIF return a #VALUE! error?
A #VALUE! error in SUMIF usually means the criteria includes a range larger than a single cell when it should be a single value or a reference to a single cell. Check that the criteria argument is not accidentally a multi-cell range.
Can SUMIF handle multiple criteria?
No. SUMIF handles exactly one criteria. For multiple criteria, use SUMIFS, or combine SUMIF results with + to add separate single-criteria sums. See the full SUMIF troubleshooting guide for advanced use cases.