Correct SUMIF Syntax
=SUMIF(criteria_range, criteria, sum_range)The criteria range is where Excel checks the condition. The sum range is where Excel adds values. These ranges should be the same shape and line up row by row.
1. Numbers Are Stored as Text
If your criteria looks like 100 but is stored as text, SUMIF may not match it correctly. This happens after CSV imports, copied web data, and accounting exports. Convert the criteria column to real numbers, or use a helper column with =VALUE(A2).
2. Criteria and Sum Ranges Do Not Match
=SUMIF(A2:A100,"East",B2:B50) is dangerous because the ranges are different lengths. Use matching ranges like A2:A100 and B2:B100, or use Excel Tables so ranges expand together.
3. Dates Are Being Treated as Text
For date criteria, use DATE rather than typed date strings:
=SUMIF(A:A,DATE(2026,6,1),B:B)This avoids regional date confusion such as 6/1 meaning June 1 in one system and January 6 in another.
4. Extra Spaces Break Text Matches
"East" and "East " are different values. Use TRIM in a helper column or clean the source data before using SUMIF. Imported files often contain non-breaking spaces, which require =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
5. Wildcards Are Too Broad
*East* matches "East", "Northeast", and "Eastern Region". If you need an exact match, remove the asterisks. If you need starts-with logic, use East*.
6. Hidden or Filtered Rows Are Still Included
SUMIF does not ignore hidden rows. If you filter the data and expect only visible rows to be summed, use SUBTOTAL or AGGREGATE with a helper column, or filter the source data first.
Fast Troubleshooting Checklist
- Check whether the result is zero, wrong, or an error.
- Confirm the criteria exists with COUNTIF.
- Check whether numbers and dates are real values, not text.
- Make criteria_range and sum_range the same size.
- Clean spaces and non-printing characters.
- Review wildcard criteria carefully.