How to fix #VALUE! in SUMIFS
Formula (Excel and Google Sheets)
=SUMIFS(C2:C8,B2:B8,F1)Make every range the same size: =SUMIFS(C2:C8,B2:B8,F1), not C2:C8 with B2:B9.
- Result on the example
- $2,965 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
Every range in SUMIFS must have the same shape: the sum range and each criteria range. =SUMIFS(C2:C8,B2:B9,F1) has 7 rows against 8 and returns #VALUE!. With matching ranges it totals East: 2,965.
=SUMIFS(C2:C8,B2:B8,F1)Result2965
C2:C87 rows × 1 col: $1,200, $850, $430, $990, …A range: 7 cells in one column.
B2:B87 rows × 1 col: "East", "West", "East", "North", …A range: 7 cells in one column.
F1"East"The value in F1.
SUMIFS(C2:C8,B2:B8,F1)2965SUMIFS: Adds values that meet every condition.- sum_range
C2:C87 rows × 1 col: $1,200, $850, $430, $990, … - criteria_range1
B2:B87 rows × 1 col: "East", "West", "East", "North", … - criteria1
F1"East"
- sum_range
In Google Sheets
The same formula works in Google Sheets: =SUMIFS(C2:C8,B2:B8,F1). We ran it in Google Sheets mode on the same data and it returns $2,965.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B8=F1)*C2:C8) | $2,965 | The same total with SUMPRODUCT, which also needs equal-sized ranges. |
| =SUMIF(B2:B8,F1,C2:C8) | $2,965 | SUMIF for a single condition. |
Common mistakes
- SUMIF doesn't error on mismatched ranges: it quietly resizes the sum range from its first cell, which can total the wrong rows.
- Criteria longer than 255 characters also return #VALUE!.
- A SUMIFS that points into another workbook returns #VALUE! while that workbook is closed. Open it, or use SUMPRODUCT.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Fix #VALUE! in SUMIFS
F2, total the amounts for the region in F1, with every range in the formula the same size.Questions
How do I add a date condition?
Add a range and criteria pair: =SUMIFS(C2:C8,B2:B8,F1,A2:A8,">="&DATE(2026,10,5)).
Why does SUMIFS return 0 instead of an error?
Nothing matched. Check for extra spaces in the data or the criteria.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.