Forum Discussion
Solved
SUM using FILTER should return empty
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
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 Replies
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)))))- Glad it worked. You're welcome!
