Sum formula and ignore blank cells to stop displaying 0

[Rodrigues]

Hi There
I'm working on a SUM formula which works fine apart from that, if ther is no values on the cells it will display 0 (zero),

=SUM(AE5,AG5,AH5,AJ5,AK5,AR5,AS5,AT5,AU5,AV5,AW5,AY5,AZ5,BA5,BB5,BC5,BD5,BE5,BF5,BG5,BH5,BI5,BJ5,BK5,BM5,BN5)
any help, it will be much appreciated.
regards
R

 

[NARAYANK991]

Hi ,

If you can use the IFERROR function , try this :

=IFERROR(1/1/SUM(AE5,AG5,AH5,AJ5,AK5,AR5,AS5,AT5,AU5,AV5,AW5,AY5,AZ5,BA5,BB5,BC5,BD5,BE5,BF5,BG5,BH5,BI5,BJ5,BK5,BM5,BN5),"")

Narayan

 

[Rodrigues]

Hi Narayan
Firstly thank you for your prompt help, did try the formula and does not show 0 on empty cells, the only problem now I have is that is not calculating properly the sum (formula on column BR), show decimals places, if format cells and remove decimal places returns 0 tried to play with formula without success not sure what I'm missing.
Could you please help?
Thanks

 

[NARAYANK991]

Hi ,

Sorry , my mistake - missing parentheses !

See the file now.

Narayan

 

[Rodrigues]

Thank you very much, you are a star.
Regards
Americo