OFFSET calculation

Laetitia · 10/10/24

Hello,

I am wondering if I could have some help on a calculation matter.
As I have tried to find quickly some answers on the forum, it seems that you can not use the OFFSET function from Excel in Smartsheet. Right now I was using it in column H to calculate the stock of the compounds in the inventory.

The goal is to add the values of column O to find the value of column H. The OFFSET function I had was the following one:

=SUM(O3:OFFSET(O3,MATCH(TRUE,INDEX(ISBLANK(O3:O3002),0),0)-1,0))

Will something be able to replace it in an easy manner?

Thanks in advance for your help!

jmyzk_cloudsmart_jp · 10/11/24

Hi @Laetitia

From your Excel sheet's image, I guess you are trying to calculate in Column H, if Column O in the same row is blank, the SUM of Column O values from the next row down to the next blank row.

To do that, I added a helper column, Row, equivalent to the row position in Excel.

Then, I added another helper column, Next Blank Row, with the following formula;

Next Blank Row

=IF(ISBLANK(O@row), IFERROR(INDEX(COLLECT(Row:Row, O:O, ISBLANK(@cell), Row:Row, >Row@row), 1), MAX(Row:Row) + 1))

The formula works as follows:

IF(ISBLANK(O@row), ...): This checks if the current cell in the column O is blank. If it's blank, the formula proceeds with the IF block; otherwise, it does nothing.
IFERROR(...): This handles potential errors in the nested INDEX(COLLECT(...)) function. If an error occurs (e.g., no matching values are found), it defaults to the value in the MAX(Row:Row) + 1 part.
The result is a list of row numbers that meet both conditions.
- COLLECT(Row:Row, O:O, ISBLANK(@cell), Row:Row, >Row@row):
  - Row:Row: Specifies the row numbers to be collected.
  - O:O, ISBLANK(@cell): This condition filters rows where the corresponding cell in the column O is blank.
  - Row:Row, >Row@row: This condition filters rows where the row number is greater than the current row (Row@row).
MAX(Row:Row) + 1: If no matching rows are found (which would lead to an error in INDEX), the formula returns the next sequential row number by taking the maximum row number in the sheet (MAX(Row:Row)) and adding

This formula checks if the current row in the column O is blank. If it is, it tries to find the first row number after the current one where the column O is also blank. If such a row is found, it returns that row number; if not, it returns the next available row number based on the maximum row number in the sheet.

SUMIFS formula at N

Using the Next Blank Row, we can calculate the desired sum with the following formula using the SUMIFS function;

=IF(ISBLANK(O@row), SUMIFS(O:O, Row:Row, >Row@row, Row:Row, <[Next Blank Row]@row))

If the current row in column O is blank, the formula sums the values in column O between the current row and the next blank row in column O.

SUMIFS(O:O, Row:Row, >Row@row, Row:Row, <[Next Blank Row]@row):

This sums up the values in the column O based on the conditions specified by SUMIFS.

Here's a breakdown of the conditions:

O:O: This is the range of values to be summed (values in column O).
Row:Row, >Row@row: This condition specifies that only rows with a row number greater than the current row (Row@row) should be included in the sum.
Row:Row, <[Next Blank Row]@row: This condition specifies that only rows with a row number less than the [Next Blank Row]@row should be included. [Next Blank Row]@row refers to a reference or formula that identifies the next row where there is a blank in the column O.

https://app.smartsheet.com/b/publish?EQBCT=2c67d5f6393042e292fe5d7a1643ff02

jmyzk_cloudsmart_jp · 10/11/24