# How to fix #VALUE! in SUMIFS

Make every range the same size: =SUMIFS(C2:C8,B2:B8,F1), not C2:C8 with B2:B9.

**Excel:** `=SUMIFS(C2:C8,B2:B8,F1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Region | Amount |  | Region | East |
| 2026-10-01 | East | 1200 |  | Total |  |
| 2026-10-02 | West | 850 |  |  |  |
| 2026-10-05 | East | 430 |  |  |  |
| 2026-10-07 | North | 990 |  |  |  |
| 2026-10-08 | East | 615 |  |  |  |
| 2026-10-12 | West | 305 |  |  |  |
| 2026-10-14 | East | 720 |  |  |  |

Formula in F2: `=SUMIFS(C2:C8,B2:B8,F1)`

Result: $2,965

## 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.

## Other ways

- `=SUMPRODUCT((B2:B8=F1)*C2:C8)`: The same total with SUMPRODUCT, which also needs equal-sized ranges.
- `=SUMIF(B2:B8,F1,C2:C8)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sumifs-value-error