Removing #CALC! error

[JTL9161]

I have this formula:

=FILTER(B$2:B$22,(C$2:C$22="")*(B$2:B$22<>""))

But when the filter is empty I get the #CALC! error.

Suggestions on how to remove the error and replace it with a blank? I thought I had it in the formula but I guess not.


Thank you,
James

 

[Joe4]

How about just wrapping it in an IFERROR function, i.e.

=IFERROR(FILTER(B$2:B$22,(C$2:C$22="")*(B$2:B$22<>"")),"")

 

[Phuoc]

Try this:

=FILTER(B$2:B$22, (C$2:C$22="") * (B$2:B$22<>""),"")

 

[Joe4]

Ah yes, good catch! I forgot that the FILTER function already has an argument to account for the case where nothing matches, so it is unnecessary to use IFERROR!

See here for more details on the function: FILTER Function

 

[JTL9161]

Thank you both.