Results 1 to 11 of 11
Thread: do not Sum if a cell is blank

20101126, 13:03 #1
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Attached is example
I need to carry a balance (column C) with new charges (column B). The balance is carrying over from the previous balance [10] when I have no entry in the charge column [B5], I would like to have no balance show on that row [C5] and following balance cells {C6 & down] untiI I have a charge[number] in that row[C5]. I assume I need a "sum C, If B >0" type of formula, but none I tried works.
TIA

20101126, 13:24 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,488
 Thanks
 376
 Thanked 1,472 Times in 1,340 Posts
Len,
What you might want to try is to set conditional formatting so that if there is no charge the running total column cell is formatted with the foreground color the same as the background color {the value will still be there but not visible}. By doing this you can just copy the formula in the running total column down the column w/o worrying about exceptions.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20101126, 15:36 #3
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Thanks RetiredGeek but I tried that but I could not get it to work. How can I do this in conditional format? I assume I use "Formula is", but then what formula do I use?
Any other suggestions welcomed.

20101126, 16:06 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about in C4
=IF(B4="","",SUM($B$3:B4))
Copy this down the column...
Steve

20101126, 16:26 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,488
 Thanks
 376
 Thanked 1,472 Times in 1,340 Posts
Len,
Ok the attached sheet has the Conditional format formula.
Note to enter the formula you highlight the whole range and use the addresses of the activecell in the range to construct the formula, i.e. if C4:C17 in the example is highlighted you would use B4 as your reference in the formula. =CELL("Contents",B4)=0. Excel will automatically adjust the formula for the other cells.
Steve, which would be more efficient your Sum formula or the conditional formatting?May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20101126, 16:47 #6
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Thanks SteveA.
That is what I wanted, but did not know how to write the formula. Once I see it it is obvious. Since I wanted to carry an original balance in C3, I just added that to the formula. Works as needed.
=IF(B4="","",SUM($B$3:B4)+($C$3)
I was using =0 and could not get it to work. Now I see I did not want "0" because it would not work with a blank.
I was trying to do the math with C4+B5 [add charge to last balance] to get new balance. =IF(B5="","",SUM(C4+B5))
Both work fine.
Again, thanks.

20101126, 19:21 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
RetiredGeek
Instead of:
=CELL("Contents",B4)=0
You can just use the simpler:
=B4=0
Len,
Instead of
=IF(B4="","",SUM($B$3:B4)+($C$3)
You can use:
=IF(B4="","",SUM($B$3:B4,$C$3)
And having a blank in C3 will not give an error.
The formula:
=IF(B5="","",SUM(C4+B5))
Will not work correctly if you have (and perhaps this will never come up) some blank cells in B and some ones down further in the rows. Also the one I give allows inserting rows within the range without the formulas becoming corrupted
Steve

20101126, 23:08 #8
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,488
 Thanks
 376
 Thanked 1,472 Times in 1,340 Posts
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20101127, 08:28 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I think the sum makes more sense. conditional format checks all the cells in col B just as the IF does. The if (I don't think) sums the cells if does nto have to and the cond formatting will have to. Also "masking" with conditional format works on the display but does not always work with printing...
Steve

20101127, 12:03 #10
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,488
 Thanks
 376
 Thanked 1,472 Times in 1,340 Posts
Steve,
Good points...thanks again.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20101129, 08:45 #11
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
Conditional formatting is also volatile.
Regards,
Rory
Microsoft MVP  Excel