How can I make a blank cell in a formula cell with a range of cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vi - 21 Jun 2007 14:17 GMT

Example: I have a column of numbers I want to add E2:E20 with the total in
E21. If there are no numbers in E2-E20, how can I make E21 to be blank
instead of showing a zero?
Thanks

Reply to this Message

Max - 21 Jun 2007 14:20 GMT

One way ..

In E21:
=IF(COUNT(E2:E20),SUM(E2:E20),"")

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Example: I have a column of numbers I want to add E2:E20 with the total in
> E21. If there are no numbers in E2-E20, how can I make E21 to be blank
> instead of showing a zero?
> Thanks

Reply to this Message

Mike H - 21 Jun 2007 14:21 GMT

Try this in E21

=IF(SUM(E1:E20)>0,SUM(E1:E20),"")

Mike

> Example: I have a column of numbers I want to add E2:E20 with the total in
> E21. If there are no numbers in E2-E20, how can I make E21 to be blank
> instead of showing a zero?
> Thanks

Reply to this Message

Vi - 21 Jun 2007 14:34 GMT

Thanks - it works

> Try this in E21
>
[quoted text clipped - 6 lines]
> > instead of showing a zero?
> > Thanks

Reply to this Message

Max - 21 Jun 2007 14:46 GMT

Admit I'm surprised you say it works, as that's not exactly what you asked
for, viz:

> .. If there are no numbers in E2-E20,
> how can I make E21 to be blank
> instead of showing a zero?

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks - it works
>
>> Try this in E21
>>
>> =IF(SUM(E1:E20)>0,SUM(E1:E20),"")

Reply to this Message

Vi - 21 Jun 2007 14:33 GMT

Thanks - it works.

> Example: I have a column of numbers I want to add E2:E20 with the total in
> E21. If there are no numbers in E2-E20, how can I make E21 to be blank
> instead of showing a zero?
> Thanks

Reply to this Message