Author |
Message |
Brian #1 / 10
|
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 |
|
|
Jack #2 / 10
|
SUM, If a cell is blank do not SUM the Range
=IF(B3="","",A3+B3)
|
Sun, 26 Sep 2004 02:06:03 GMT |
|
|
Peo Sjoblo #3 / 10
|
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 |
|
|
hans #4 / 10
|
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 |
|
|
Brian #5 / 10
|
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 |
|
|
Brian #6 / 10
|
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
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 |
|
|
Brian #7 / 10
|
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 |
|
|
gavi #8 / 10
|
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 |
|
|
Jarek Kujaw #9 / 10
|
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
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 |
|
|