# How to fix the #CALC! error in FILTER in Excel

#CALC! means no row matched. Give FILTER its third argument: `=FILTER(A2:E11,D2:D11=H1,"No matches")` shows a message instead.

**Excel:** `=FILTER(A2:E11,D2:D11=H1,"No matches")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=IFNA(FILTER(A2:E11,D2:D11=H1),"No matches")`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Order | Date | Customer | Region | Amount |  | Region | Central |
| SO-1041 | 2026-02-23 | Northwind | West | 1840 |  |  |  |
| SO-1042 | 2026-03-02 | Contoso | East | 420 |  |  |  |
| SO-1043 | 2026-03-09 | Fabrikam | West | 395 |  |  |  |
| SO-1044 | 2026-03-15 | Litware | North | 1720 |  |  |  |
| SO-1045 | 2026-03-21 | Adatum | South | 2210 |  |  |  |
| SO-1046 | 2026-03-31 | Proseware | East | 1955 |  |  |  |
| SO-1047 | 2026-04-06 | Wingtip | West | 610 |  |  |  |
| SO-1048 | 2026-04-14 | Tailspin | South | 380 |  |  |  |
| SO-1049 | 2026-04-20 | Lucerne | North | 1100 |  |  |  |
| SO-1050 | 2026-04-28 | Contoso | East | 865 |  |  |  |

Formula in G3: `=FILTER(A2:E11,D2:D11=H1,"No matches")`

Result: No matches

## How it works

When no row passes the test, FILTER has nothing to return, and Excel shows that as #CALC! (an empty array). The optional third argument, if_empty, is what FILTER returns instead. Here no order is from Central, so the cell shows the message; change `H1` to West and the rows spill as usual.

Google Sheets' FILTER has no if_empty argument and returns #N/A, so the Sheets version wraps it in IFNA.

## Other ways

- `=IFERROR(FILTER(A2:E11,D2:D11=H1),"No matches")`: Works in Google Sheets too, but it hides every error, including a #VALUE! from ranges of different sizes.
- `=FILTER(A2:E11,D2:D11=H1,"")`: An empty string leaves the cell blank, which suits dashboards. (Shows a blank cell instead of the message.)

## Common mistakes

- If you can see matching rows but still get #CALC!, the values differ: a trailing space in the data, or a number stored as text on one side.
- #VALUE! is a different problem: the condition isn't the same height as the array, such as `A2:E11` with `D2:D12`.
- An Excel FILTER with a third argument doesn't carry over to Google Sheets, where the third argument is read as another condition.

## Questions

**What does the #CALC! error mean?**

A calculation produced something Excel can't show, most often an empty array. FILTER with no matching rows is the usual cause.

**How do I show 0 instead when I sum the filtered values?**

Use 0 as if_empty: `=SUM(FILTER(E2:E11,D2:D11=H1,0))`. SUMIFS also returns 0 for no matches.

**How do I filter on two conditions?**

Multiply them: `=FILTER(A2:E11,(D2:D11=H1)*(E2:E11>1000),"No matches")`.

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/fix-filter-calc-error