Prev: SD03*10 -how do I just get the numbers at the end in cell after *
Next: Cell Function/Font Color
From: Frustrated by Averages on 12 May 2010 16:05
I have the following formula in place:
=IF(SUM(I14:N14)>1,SUM(I14:N14),"")

The problem I have is when zeros are legitimately entered in the reference
fields the sum formula leaves the cell blank instead of summing the cells
across to display a zero.

In essence how do I get the field to be blank when the reference cells are
blank and display a zero when the reference fields are filled with zeros?

I am sure it is simple, but my brain is locked up!
Thanks for your help in advance.

From: "David Biddulph" groups [at] on 12 May 2010 16:27
Perhaps you intended to say not
=IF(SUM(I14:N14)>1,SUM(I14:N14),"")
but
=IF(COUNT(I14:N14)>1,SUM(I14:N14),"")
or
=IF(COUNT(I14:N14)=0,"",SUM(I14:N14)) ?
--
David Biddulph


"Frustrated by Averages" <FrustratedbyAverages(a)discussions.microsoft.com>
wrote in message news:EA8BDABA-390F-4264-B999-64AB7B5E1E56(a)microsoft.com...
> I have the following formula in place:
> =IF(SUM(I14:N14)>1,SUM(I14:N14),"")
>
> The problem I have is when zeros are legitimately entered in the reference
> fields the sum formula leaves the cell blank instead of summing the cells
> across to display a zero.
>
> In essence how do I get the field to be blank when the reference cells are
> blank and display a zero when the reference fields are filled with zeros?
>
> I am sure it is simple, but my brain is locked up!
> Thanks for your help in advance.
>

From: T. Valko on 12 May 2010 16:33
Try this...

=IF(COUNT(I14:N14),SUM(I14:N14),"")

--
Biff
Microsoft Excel MVP


"Frustrated by Averages" <FrustratedbyAverages(a)discussions.microsoft.com>
wrote in message news:EA8BDABA-390F-4264-B999-64AB7B5E1E56(a)microsoft.com...
>I have the following formula in place:
> =IF(SUM(I14:N14)>1,SUM(I14:N14),"")
>
> The problem I have is when zeros are legitimately entered in the reference
> fields the sum formula leaves the cell blank instead of summing the cells
> across to display a zero.
>
> In essence how do I get the field to be blank when the reference cells are
> blank and display a zero when the reference fields are filled with zeros?
>
> I am sure it is simple, but my brain is locked up!
> Thanks for your help in advance.
>


From: Frustrated by Averages on 12 May 2010 18:19
That worked. Thanks for your help!

"David Biddulph" wrote:

> Perhaps you intended to say not
> =IF(SUM(I14:N14)>1,SUM(I14:N14),"")
> but
> =IF(COUNT(I14:N14)>1,SUM(I14:N14),"")
> or
> =IF(COUNT(I14:N14)=0,"",SUM(I14:N14)) ?
> --
> David Biddulph
>
>
> "Frustrated by Averages" <FrustratedbyAverages(a)discussions.microsoft.com>
> wrote in message news:EA8BDABA-390F-4264-B999-64AB7B5E1E56(a)microsoft.com...
> > I have the following formula in place:
> > =IF(SUM(I14:N14)>1,SUM(I14:N14),"")
> >
> > The problem I have is when zeros are legitimately entered in the reference
> > fields the sum formula leaves the cell blank instead of summing the cells
> > across to display a zero.
> >
> > In essence how do I get the field to be blank when the reference cells are
> > blank and display a zero when the reference fields are filled with zeros?
> >
> > I am sure it is simple, but my brain is locked up!
> > Thanks for your help in advance.
> >
>
> .
>
 | 
Pages: 1
Prev: SD03*10 -how do I just get the numbers at the end in cell after *
Next: Cell Function/Font Color