SUM using FILTER should return empty

[oteixeira62]

Hello to all,

I am uploading a file in which I believe the formula I entered should return EMPTY instead of 1.

Can I be doing something wrong?

Many thanks for any help,

Octavio

[#2 · Accepted solution]

@oteixeira62 

The FILTER is failing so it's going to the error check which is to show "Sem dados".  ROWS is then counting that as 1.

Try this:

=IF(OR($C$8="Sem dados",$C$8=""),"",UNIQUE(LET(mercado,FILTER(TRegistos[Origem],(TRegistos[Item]=$C$8)*(TRegistos[Origem]=$E$4)*(YEAR(TRegistos[Data])=$G$2),"Sem dados"),mundial,FILTER(TRegistos[Item],(TRegistos[Origem]=$C$8)*(YEAR(TRegistos[Data])=$G$2)*(TRegistos[Item]<>"")*(TRegistos[Item]<>0),"Sem dados"),results,IF($K$2="por mercado",mercado,mundial),IF(results="Sem dados",0,ROWS(results)))))

[#3]

@Patrick2788

Ah! I should have known, it is not a SUM...

Thanks a million,

Octávio 

[#4]

Glad it worked. You're welcome!