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

Richard Heath
Richard Heath ✭✭✭✭✭✭
edited 08/10/22 in Smartsheet Basics

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?


Best Answers