Thread: do not Sum if a cell is blank

1. 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

2. 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.

3. 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.

=IF(B4="","",SUM(\$B\$3:B4))

Copy this down the column...

Steve

5. 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?

6. 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.

7. RetiredGeek

=CELL("Contents",B4)=0

You can just use the simpler:
=B4=0

Len,
=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

8. Originally Posted by SteveA
RetiredGeek

=CELL("Contents",B4)=0

You can just use the simpler:
=B4=0
Steve, yes I realized that shortly after I posted, thanks. But my question still remains, which is more efficient using a sum which could reference hundreds or thousands of cells or the conditional formatting?

9. 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

10. Steve,

Good points...thanks again.

11. Conditional formatting is also volatile.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•