SUM, If a cell is blank do not SUM the Range 
Author Message
 SUM, If a cell is blank do not SUM the Range

Example:

A1 + B1 = C1

If B1 is blank or is a zero I do not want C1 to display
the SUM (for this row). I do want the rest of the rows to
perform their calculations though (i.e. A2 + B2 = C2,
etc...).

Is there a way to do this? (besides the obvious of
removing the calculation for the rows where one of the
fields is blank or zero).

Any assistance is greatly appreciated!



Sun, 26 Sep 2004 01:56:55 GMT  
 SUM, If a cell is blank do not SUM the Range
=IF(B3="","",A3+B3)


Sun, 26 Sep 2004 02:06:03 GMT  
 SUM, If a cell is blank do not SUM the Range
Maybe

=IF((ISBLANK(B1))+(B1=0),"",A1+B1)

will return blank if B1 is either blank or zero

--

Regards,

Peo Sjoblom

ROT13 email

Quote:

> Example:

> A1 + B1 = C1

> If B1 is blank or is a zero I do not want C1 to display
> the SUM (for this row). I do want the rest of the rows to
> perform their calculations though (i.e. A2 + B2 = C2,
> etc...).

> Is there a way to do this? (besides the obvious of
> removing the calculation for the rows where one of the
> fields is blank or zero).

> Any assistance is greatly appreciated!



Sun, 26 Sep 2004 02:14:24 GMT  
 SUM, If a cell is blank do not SUM the Range
=IF(A1<>"";IF(B1<>"";A1+B1;""))


Quote:
> Example:

> A1 + B1 = C1

> If B1 is blank or is a zero I do not want C1 to display
> the SUM (for this row). I do want the rest of the rows to
> perform their calculations though (i.e. A2 + B2 = C2,
> etc...).

> Is there a way to do this? (besides the obvious of
> removing the calculation for the rows where one of the
> fields is blank or zero).

> Any assistance is greatly appreciated!



Sun, 26 Sep 2004 02:30:15 GMT  
 SUM, If a cell is blank do not SUM the Range
I tried your suggestion. I didn't work but thank you for
your time!
Quote:
>-----Original Message-----
>=IF(A1<>"";IF(B1<>"";A1+B1;""))



>> Example:

>> A1 + B1 = C1

>> If B1 is blank or is a zero I do not want C1 to display
>> the SUM (for this row). I do want the rest of the rows
to
>> perform their calculations though (i.e. A2 + B2 = C2,
>> etc...).

>> Is there a way to do this? (besides the obvious of
>> removing the calculation for the rows where one of the
>> fields is blank or zero).

>> Any assistance is greatly appreciated!

>.



Sun, 26 Sep 2004 02:53:37 GMT  
 SUM, If a cell is blank do not SUM the Range
Your solution worked very well. I really appreciate your
post!

Thanks.

Quote:
>-----Original Message-----
>Maybe

>=IF((ISBLANK(B1))+(B1=0),"",A1+B1)

>will return blank if B1 is either blank or zero

>--

>Regards,

>Peo Sjoblom

>ROT13 email




- Show quoted text -

Quote:
>> Example:

>> A1 + B1 = C1

>> If B1 is blank or is a zero I do not want C1 to display
>> the SUM (for this row). I do want the rest of the rows
to
>> perform their calculations though (i.e. A2 + B2 = C2,
>> etc...).

>> Is there a way to do this? (besides the obvious of
>> removing the calculation for the rows where one of the
>> fields is blank or zero).

>> Any assistance is greatly appreciated!

>.



Sun, 26 Sep 2004 02:56:48 GMT  
 SUM, If a cell is blank do not SUM the Range
I tried your suggestion but it didn't work for me. Thanks
for your effort though!
Quote:
>-----Original Message-----
>=IF(B3="","",A3+B3)

>.



Sun, 26 Sep 2004 02:54:27 GMT  
 SUM, If a cell is blank do not SUM the Range

Quote:
>-----Original Message-----
>Example:

>A1 + B1 = C1

>If B1 is blank or is a zero I do not want C1 to display
>the SUM (for this row). I do want the rest of the rows
to
>perform their calculations though (i.e. A2 + B2 = C2,
>etc...).

>Is there a way to do this? (besides the obvious of
>removing the calculation for the rows where one of the
>fields is blank or zero).

>Any assistance is greatly appreciated!
>.
>Square brackets enclose exact entries required ...

c2 entry ...
[=if(b1=0,0,a1+b1)]
then copy down for a2/b2, a3/b3, etc
If you don't even want the zero to show, do
Tools/Options/General, and then uncheck "Zero Values".


Sun, 26 Sep 2004 03:32:42 GMT  
 SUM, If a cell is blank do not SUM the Range
nice formula, Peo

did not know + worked the same as OR

am gonna save it on my PC to remember



Quote:
> Maybe

> =IF((ISBLANK(B1))+(B1=0),"",A1+B1)

> will return blank if B1 is either blank or zero

> --

> Regards,

> Peo Sjoblom

> ROT13 email




- Show quoted text -

Quote:
> > Example:

> > A1 + B1 = C1

> > If B1 is blank or is a zero I do not want C1 to display
> > the SUM (for this row). I do want the rest of the rows to
> > perform their calculations though (i.e. A2 + B2 = C2,
> > etc...).

> > Is there a way to do this? (besides the obvious of
> > removing the calculation for the rows where one of the
> > fields is blank or zero).

> > Any assistance is greatly appreciated!



Sun, 26 Sep 2004 15:17:56 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. To Sum or not sum w/ blank cells

2. summing last 12 non-blank cells in range

3. AM I missing something - Sum and Cumulative Sum

4. Sum cells if not blank

5. Vlookup column will not SUM a range of cells

6. cell range does not update sum

7. Summing using formulas already in cells, not cell references, or values in cells

8. Sum() - Does Not Sum

9. SUM not summing all requested rows

10. Réf. : sum and sum not


 
Powered by phpBB® Forum Software © phpBB Group