Prevent =FILTER showing #CALC! when no values found

[MeaclH]

Hi all,

I have the following formula that works perfectly, cross checking 1 column for values with another on a different sheet. It shows what values are not present.
When all values are accounted for, it shows the #CALC! result.

=FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(I11:I111,DATA!$AM$3:$AM$19)))

Any way to have this show as blank instead when nothing is found, which is the majority of the time.

Cheers,
Hayden

 

[Fluff]

How about

=FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(I11:I111,DATA!$AM$3:$AM$19)),"")

 

[etaf]

just add another , with the IF Empty condition
=FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(I11:I111,DATA!$AM$3:$AM$19)),"")

 

[MeaclH]

works a treat. Thanks both @Fluff and @etaf !

 

[etaf]

you are welcome

 

[Fluff]

Glad we could help & thanks for the feedback.