do not Sum if a cell is blank

Topic

New Reply

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

Reply | Quote

Replies

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!
Computer Specs

Reply | Quote

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.

Reply | Quote

How about in C4
=IF(B4=””,””,SUM($B$3:B4))

Copy this down the column…

Steve

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

RetiredGeek

Instead of:

=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?

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Steve,

Good points…thanks again.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Conditional formatting is also volatile.

Reply | Quote