Formula guide · Excel and Google Sheets · Updated May 13, 2026
SUMIFS Not Working
Fix SUMIFS formulas that return zero, wrong totals, or errors because of mismatched ranges or criteria issues.
Quick Answer
SUMIFS usually fails because one criteria range is a different size, numbers are stored as text, or criteria are written in the wrong format.
Copyable Formula
=SUMIFS(D2:D100,B2:B100,"West",C2:C100,"Paid")Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, ...)Excel and Google Sheets
Worked Example
| Symptom | Check |
|---|---|
| Returns 0 | Criteria spelling and data type |
| Wrong total | Range alignment |
| Error | Range sizes |
Result: Aligned ranges and correctly written criteria usually fix SUMIFS issues.
Steps
- Confirm every range starts and ends on the same rows.
- Filter the source data manually to verify the expected total.
- Check whether numbers, dates, or IDs are stored as text.
- Build the formula one criterion at a time.
Common Mistakes
- Mixing SUMIF and SUMIFS argument order.
- Using full columns for one range and partial ranges for another.
- Writing date criteria without comparison operators.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.