How to fix the #CALC! error in Excel

The usual cause: FILTER found nothing

The sample sheet is a help-desk log. F2 holds the team to show, and a FILTER lists that team's tickets. With Support or Sales in F2 it spills the ticket numbers. Switch F2 to Billing, a team with no tickets yet, and the formula has nothing to return.

Excel cannot show "nothing" as a list, so it shows #CALC! instead. The formula is not broken. It is telling you the result is empty. The same happens when a criterion has a stray space: "Support " with a trailing space matches no row and also gives #CALC!.

Fix 1: give FILTER an if_empty value

FILTER's third argument is what to show when nothing matches. Use a short message for a report, "" to leave the cell blank, or 0 when another formula will do maths on the result.

You can build the message from the input so readers know what was searched. If F2 might be empty, test it first: =IF(F2="","Pick a team",FILTER(A2:A7,B2:B7=F2,"No "&F2&" tickets")) shows Pick a team until someone chooses one.

When FILTER sits inside SUM, COUNTA, UNIQUE or SORT

#CALC! passes straight through any function wrapped around an empty FILTER. SUM(FILTER(...)), MAX(FILTER(...)), ROWS(FILTER(...)) and UNIQUE(FILTER(...)) all return #CALC! for Billing. Give the FILTER inside its own if_empty argument, so the outer function never receives an empty array.

Pick the if_empty value for the outer function. For SUM, use 0: the formula below returns 0 hours. For MAX, 0 is only safe when it cannot be mistaken for a real result; a message is clearer.

Counting is the trap. =COUNTA(FILTER(A2:A7,B2:B7=F2,"")) returns 1 for Billing, because COUNTA counts the "" as a value. Count with =IFERROR(ROWS(FILTER(A2:A7,B2:B7=F2)),0) instead, which returns 0. Or skip FILTER and use =COUNTIF(B2:B7,F2), which also returns 0.

Other empty arrays: UNIQUE, TAKE and DROP

FILTER is not the only function that can end up with nothing. UNIQUE with its third argument set to TRUE returns only values that appear exactly once. On this sheet =UNIQUE(B2:B7,,TRUE) returns Onboarding, the only team with a single ticket. If every value repeated, it would return #CALC!.

TAKE and DROP do the same at the edges: DROP that removes every row, or TAKE with 0 rows, has nothing left and returns #CALC!. When the number of rows comes from another cell, check it can never reach the size of the data.

Nested arrays and LAMBDA

The second family of #CALC! causes comes from Excel 365's LAMBDA helpers. BYROW and MAP expect one value per row or per item. If the LAMBDA returns a whole list for each one, for example a FILTER of each team's tickets, Excel cannot fit a list inside a single cell of the result and shows #CALC!. Make the LAMBDA return one value, such as a SUM or a TEXTJOIN of the list, or build the full list with a single FILTER instead.

A LAMBDA typed into a cell on its own, such as =LAMBDA(x,x*2), also shows #CALC!, because it defines a function but never runs it. Add the argument in brackets to test it, =LAMBDA(x,x*2)(5), or save it as a name in Formulas > Name Manager and call it by that name.

#CALC! vs #SPILL!, #N/A, and IFERROR

#SPILL! means the formula has results but no empty room to show them. #CALC! means there are no results at all. #N/A is what lookups return when a single value is missing: =XLOOKUP(F2,B2:B7,A2:A7) gives #N/A for Billing, while SUMIFS for the same team quietly gives 0.

IFNA does not catch #CALC!. It only replaces #N/A, so the formula below still shows the error. IFERROR does catch it, but it also hides typos and #VALUE! from a broken condition. Prefer if_empty: it only covers "no matches" and leaves real mistakes visible.