SUM of consecutive cells until blank cell is reached in the column

Brett Hill

Hi, i am trying to SUM any given number of consecutive cells in column A and need it to stop once a black cell is reached. For instance The SUM will be column A, row 2 to 6 (7 is blank) and I am wanting the SUM to display in column B row 1. This will repeat throughout the sheet, but the SUM result will always be column B and the row above the first number that has just been counted. i hope this makes sense. Any advise / help would be appreciated. Brett

Jim Hook

Brett,

What you are trying to do is quite challenging, if not impossible, in Smartsheet. I had a recent situation where I needed to do something very similar to compress multiple rows with timekeeping data into a single row if the project name and functional area were the same for multiple people's hours in a week. In my case I ended up using three columns in order to identify rows that were the summing rows, the number of rows to be summed and finally the sum of the rows. The column with the sum of the rows had a lengthly nested IF() formula in it that would sum the rows of interest based on the number of rows to be summed column. In my case I needed to sort the incoming data by project name and functional area before I could start processing it and then convert the results to values then resort to get just the summary rows together. This multistep process  would be error prone in Smartsheet so I exported the whole sheet to Excel and then copied the data of interest into another Excel sheet where I had created a Macro that would do it all in one click. It works great but it won't work for your particular needs since you need to keep the results in the original Smartsheet.

 

I noticed that the recent formula update in Smartsheet includes some new advanced functions that might be helpful for doing what you need but I haven't had the time to investigate them.

 

Good luck.

Jim Hook

Brett,

 

I had some time this morning and came up with a way to do what you were looking for. It's not trivial but less difficult than I expected. Share your email address and I'll share an example sheet with you that does it.

Brett Hill

thanks Jim for your help,

brett@specfurn.com.au

Mat

Is it possible to share how you did manage this? I am in the same situation and I don't know how to deal with it