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

Q: 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.

Q: How do I filter on two conditions?

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

Excel formula

=FILTER(A2:E11,D2:D11=H1,"No matches")

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

Result on the example: No matches in G3
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Different formula, see below

See it work

The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.

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.

=FILTER(A2:E11,D2:D11=H1,"No matches")

Result"No matches"

A2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", …
A range: 10 rows × 5 columns.
D2:D1110 rows × 1 col: "West", "East", "West", "North", …
A range: 10 cells in one column.
H1"Central"
The value in H1.
D2:D11=H1{10 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …
The = operator tests whether the two sides are equal.
FILTER(A2:E11,D2:D11=H1,"No matches")"No matches"
FILTER: Rows of a range that meet every condition. #N/A when none match.
- rangeA2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", …
- condition1D2:D11=H1{10 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …
- condition2, ..."No matches"

In Google Sheets

Google Sheets needs a different formula: =IFNA(FILTER(A2:E11,D2:D11=H1),"No matches"). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=IFERROR(FILTER(A2:E11,D2:D11=H1),"No matches")	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,"")	Different result	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.

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 the #CALC! error in FILTER

In G3, return the orders for the region in H1, and show "No matches" when there are none.

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").

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.