OFFSET calculation
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!
Best Answer
-
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 columnO
is blank. If it's blank, the formula proceeds with theIF
block; otherwise, it does nothing.IFERROR(...)
: This handles potential errors in the nestedINDEX(COLLECT(...))
function. If an error occurs (e.g., no matching values are found), it defaults to the value in theMAX(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 columnO
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 inINDEX
), 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 columnO
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 bySUMIFS
.
Here's a breakdown of the conditions:
O:O
: This is the range of values to be summed (values in columnO
).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 columnO
.
Answers
-
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 columnO
is blank. If it's blank, the formula proceeds with theIF
block; otherwise, it does nothing.IFERROR(...)
: This handles potential errors in the nestedINDEX(COLLECT(...))
function. If an error occurs (e.g., no matching values are found), it defaults to the value in theMAX(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 columnO
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 inINDEX
), 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 columnO
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 bySUMIFS
.
Here's a breakdown of the conditions:
O:O
: This is the range of values to be summed (values in columnO
).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 columnO
.
-
Hey there :)
That was just an incredible response and does exactly what needed doing!! Thank you very much for your help -
Happy to help!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!