Adding Cells together - but ignoring blank cells - problem with output.

Richard Heath

I've created a Sheet that collects data from various sheets and collates it to enable a Cashflow Graph to be produced.

The rows noted as "Forecast", "Actual" & "Forecast Cumulative" all work as intended with cells appropriately left blank where there is no value - this ensures the graphs don't bounce back and forth to $0.

Row [Actual Cumulative] is where I am struggling. It basically takes the value in "Actual" and adds the prior month - pretty straightforward. Because some of "Actual" cells are BLANK, my formulas treat the cells like text, rather than numbers.

The above results in 500 (not $500). You'll note in the top graphic, that this formula results in strings of text, rather than dollar values. (And yes, I've tried to change the number type to $).

I've also tried =IF([ColumnXX]3 <> "", [ColumnXX]3 + [ColumnXX]@row, "") - if a column is blank, return nothing, if it has a value, add it to the previous month. This still results in strings of data.

I've also tried to incorporate a ISBLANK formula - this results in mixture of outcomes

Any way to overcome this?

Toufong Vang

Hi, @Richard Heath.

You can use the function VALUE() to convert text to number before adding them.

= IF( ISBLANK([Column6]3), "", VALUE([Column6]3) + VALUE([Column5]3) )

Richard Heath

Thanks @Toufong Vang . That has worked exactly how I need it to.

Much appreciated.

Toufong Vang

Hi, @Richard Heath.

You can use the function VALUE() to convert text to number before adding them.

= IF( ISBLANK([Column6]3), "", VALUE([Column6]3) + VALUE([Column5]3) )

Richard Heath

Thanks @Toufong Vang . That has worked exactly how I need it to.

Much appreciated.