Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the SUMIF error
Excel & Google SheetsWhat SUMIF means
SUMIF 'not working' covers several symptoms: returning 0 when a non-zero sum is expected, returning #VALUE!, returning the wrong number, or the formula showing as text. The root cause is almost always a criteria problem, a range mismatch, or a data-type inconsistency in the sum_range.
Common causes
- The criteria is a comparison like >100 written without quotes, causing a #VALUE! error or unexpected behaviour.
- The sum_range is omitted — when omitted SUMIF sums the criteria_range itself, which is correct only if that is the intended range.
- Using a cell reference that contains operator text like ">100" combined with string concatenation incorrectly — the concatenation itself is fine but missing an & joins nothing.
- The formula is in a cell formatted as Text, so it renders as the literal formula string rather than computing.
- Cross-sheet references use the wrong syntax — in Google Sheets a SUMIF referencing another tab must use a full range reference, not just the column letters.
Example fix
Broken
=SUMIF(B2:B100,>100,C2:C100)Fixed
=SUMIF(B2:B100,">100",C2:C100)The comparison operator > must be inside a quoted string. Without quotes it is a syntax error in most spreadsheet engines.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Always quote comparison operators: =SUMIF(B:B,">100",C:C). To use a cell reference for the threshold: =SUMIF(B:B,">"&E2,C:C).
- 2Add the sum_range explicitly: =SUMIF(A:A,"Criteria",B:B). Only omit it if you intentionally want to sum the criteria_range values.
- 3If the cell shows formula text: select the cell, press Ctrl+1 (Excel) or Format > Number (Sheets), change from Text to General, then press F2 and Enter to re-evaluate.
- 4For cross-sheet references in Google Sheets, reference the full range: =SUMIF(Sheet2!A:A,"value",Sheet2!B:B) rather than relying on implicit sheet context.
- 5Test by temporarily replacing the criteria with a hard-coded known value to confirm the formula structure is correct before introducing cell references or operators.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every SUMIF and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
How do I use SUMIF with a dynamic criteria value from another cell?
Concatenate the operator and the cell: =SUMIF(B:B,">"&E2,C:C). This joins the operator string with the number in E2 at calculation time.
Does SUMIF work with dates as criteria?
Yes, but the date must be a real date value, not text. Use =SUMIF(A:A,">"&DATE(2024,1,1),B:B) to sum rows after 1 Jan 2024.
Can SUMIF sum multiple criteria — for example, two categories?
SUMIF only handles one criteria range and one criteria value. For multiple criteria, use SUMIFS, which accepts pairs of criteria_range and criteria.
More on SUMIF & SUMIFS
See all →Reference
SUMIF
Add up the cells in a range that meet a single condition.
How-toSUMIF vs SUMIFS: when to use each in Excel and Sheets
Understand when to use SUMIF (one condition) versus SUMIFS (multiple conditions) for conditional summing in Excel and Google Sheets.
Error fixFix 0
SUMIF returns 0 instead of the expected total in Excel and Google Sheets — fix number-as-text, criteria mismatch, range size errors, and data-type issues.
ReferenceSUMIFS
Sum values that meet multiple conditions at once.
How-toHow to use SUMIF with a date range
Use SUMIF with a date comparison criterion to sum values only for dates on or after (or before) a specific date in your data range.
Error fixFix 0
SUMIFS returns 0 in Excel and Google Sheets — fix criteria order errors, range size mismatches, data-type problems, and AND logic misunderstandings.