Excel troubleshooting

Why Is SUMIF Not Working?

Fix SUMIF formulas that return zero, wrong totals, or unexpected results by checking criteria, range sizes, dates, and numbers stored as text.

Problem

  • SUMIF usually fails because criteria do not match the data or because range and sum_range are misaligned.

Quick fix

  • Check the criteria syntax, make range and sum_range the same shape, and confirm that numbers and dates are real Excel values.

Common SUMIF problems

  • Criteria text does not exactly match the values in the criteria range.
  • Dates are stored as text or typed in a regional format Excel does not recognize.
  • The criteria range and sum_range start on different rows or have different sizes.

Reliable fixes

  • Use quotes around criteria like \
  • or operators like \
  • .
  • Use DATE(year, month, day) for date criteria instead of ambiguous typed dates.
  • Use SUMIFS when you need more than one condition.

Overview

  • SUMIF problems are usually criteria syntax, text vs number, or misaligned ranges — not a broken Excel install. Walk through these checks before rewriting the whole report.

Debug with Evaluate Formula

  • Select the cell → Formulas → Evaluate Formula.
  • Confirm criteria evaluates to text in quotes or a number.
  • Watch whether the criteria_range reference shifts when copied.

Criteria patterns that fail silently

  • Forgot quotes around text: East vs "East".
  • Used =SUMIF(Amount,">F1",Amount) instead of ">"&F1.
  • Trailing space in source data — TRIM helper column.

When to switch to SUMIFS

  • Second condition appeared after the report was built.
  • [SUMIFS function](/functions/sumifs/) · [SUMIFS examples](/blog/sumifs-excel-examples/).

Quick answer: sumif not working

  • SUMIF often returns 0 when criteria text does not exactly match, dates are text, or sum_range and criteria_range differ in height. SUMIF ignores AutoFilter — it sums hidden rows too.
  • >"&F1 for thresholds from a cell.',
  • *east*".
  • Multiple conditions → [SUMIFS](/functions/sumifs/).

Evaluate Formula debug steps

  • Select the SUMIF cell → Formulas → Evaluate Formula to see how Excel resolves criteria.
  • Confirm the criteria operator is inside quotes and the cell reference uses & when needed.

Wildcards vs cell-reference criteria

  • Wildcard in quotes: ">100" or "*east*".
  • Threshold from cell: ">"&F1 — do not put the cell reference inside the quotes alone.
  • Upgrade path: [SUMIFS function](/functions/sumifs/) for multiple conditions.

People also ask

  • SUMIF not summing filtered rows only? — SUMIF ignores filters unless you use SUBTOTAL on visible cells.
  • SUMIF with dates not working? — Confirm real Excel dates, not text.

Frequently asked questions

  • SUMIF returns 0 but values look correct? Check exact text match, date serial numbers, and that sum_range aligns row-for-row with criteria_range.
  • SUMIF double-counts after refresh? Duplicate rows in source data — dedupe or use COUNTIFS to audit keys first.
  • SUMIF with dates from CSV? Import may leave dates as text. Use DATEVALUE or Text to Columns on General.
  • Does SUMIF ignore hidden filtered rows? Yes — SUMIF sums all cells in the range, not only visible rows. Use SUBTOTAL on visible cells or SUMIFS on a helper column for filter-aware totals.
  • Why does SUMIF return 0 with dates? Dates may be text. Confirm with ISNUMBER and use DATE or VALUE to normalize before applying criteria.
  • SUMIF vs SUMIFS — wrong sum with one criterion? Check that sum_range size matches criteria_range in SUMIFS. For one rule, SUMIF is simpler and less error-prone.