Hi vijaynpd,
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
Regarding uploading file,please see the below link:
http://chandoo.org/forums/topic/posting-a-sample-workbook
Now regarding your question, lets us talk about the second part of your requirement where you want to remove the "zero" from chart data label (correct me if I am wrong!).
You can do this by using custom number format. Select the chart data label> right click on any data label> select "format data labels"> select "Number" from left hand side> select "custom" from category> in Format code box write 0.0;-0.0;> click on add and close it.
The data label with zero should be removed.
Now regarding your first part, where you want your formula to return "blank" when there is no value in the range AF1:AF9, you can use the either of the below formulae:
=IF(AF1:AF9="","",SUM(AF1:AF9)) enter as array formula by pressing CTRL+SHIFT+ENTER from your key board; or,
=IF(SUM(AF1:AF9)=0,"",SUM(AF1:AF9)) ENTER
Please let us know if this is fine.
Kaushik
