How to refer to slice within named column?

[Cerin]

I have several columns referring to distinct data which I want to reference in aggregate calculations. To help keep those formulas readable, I’ve named the columns. e.g. Instead of having a formula like SUM(H) I have =SUM(PRODUCT_SAMPLES).

This seems to work fine if I want to aggregate an entire column. However, for some formulas, I only want to reference a specific slice. How do you slice a named column? To re-use the previous example, if I only wanted to count the last 20 rows of the “PRODUCT_SAMPLES” column, I would do =SUM(H1:H20), but I don’t know how to write that using the name. Entering =SUM(PRODUCT_SAMPLES1:PRODUCT_SAMPLES20) returns an error code.

Note, although the question sounds similar, I’m not asking what this question seems to be asking, and trying =SUM(INDEX(PRODUCT_SAMPLES;1;20)) does not work.

[SM_Riga]

Hello @Cerin

While INDEX function allows you to refer one single cell (when row or column parameter is defined and is not 0) or the whole column/row in the range (row/column parameter set to 0 or is omitted), there is an OFFSET function, which brings ability to define new size of the referred range. So you shall pass your named range as a reference parameter to OFFSET, then pass row_offsetparameter to move first referenced cell of the range down the column if needed, left col_offsetset to 0 (if you use only one column wide named range, you do not need to move reference left/right) and then via 4th and 5th parameters you can set the new size of the range. So, to reference rows from 1 to 20 of your PRODUCT_SAMPLES range, you can use:

=OFFSET(PRODUCT_SAMPLES;0;0;20;1)

Then just nest it in the SUM formula:

=SUM(OFFSET(PRODUCT_SAMPLES;0;0;20;1))