CODEWITHSUNDEEP
google-sheets
Keisuke Nagakawa 永川 圭介
Keisuke Nagakawa 永川 圭介

Reputation: 1125

How to select subset of a range in Google Spreadsheet?

I want to extract subset of a range from another range by selecting starting columns and length.

For example, how can I extract B1:C1 from another range A1:D1 by indicating starting columns 2 as in the image attached?

enter image description here

Thanks!

Upvotes: 2

Views: 1749

Answers (2)

The God of Biscuits
The God of Biscuits

Reputation: 3177

This is easily achievable with the OFFSET function:

=sum(offset(Range,,StartCol-1,,Width))

So for your specific example, the expression would be:

=sum(offset(A1:D1,,2-1,,2))

Upvotes: 3

Tanaike
Tanaike

Reputation: 201388

In your question, how about the following answer? In this answer, I used a custom function created by Google Apps Script. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script.

function someKindOfFunction(range, startColumn, length) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const orgRange = sheet.getRange(range);
  const srcRange = orgRange.offset(0, startColumn - 1, orgRange.getNumRows(), length);
  const values = srcRange.getValues();
  return values;
}
  • When you use this script, for your showing sample Spreadsheet, please put a custom function of =SUM(someKindOfFunction("A1:D1",2,2)) to a cell. By this, 5 is returned.

Testing:

When this script is used for your showing sample Spreadsheet, the following result is obtained.

enter image description here

In this case, the range is given as a string like =SUM(someKindOfFunction("A1:D1",2,2)). Please be careful about this.

Note:

  • At your showing image, you are using =SUM(someKindOfFunction(A1:D1,startingrow: 2, length: 2)).
    • From this, I guessed someKindOfFunction might be a custom function.
    • And, from your question, I guessed "startingrow" might be "startingcolumn".

Added:

As another direction, when you use A1:D1 as the range instead of the string value, how about the following sample script? In this sample script, the array is directly used. But, I'm not sure whether this is your expected direction.

function someKindOfFunction(values, startColumn, length) {
  return values.map(r =>
    r.flatMap((c, j) => {
      const start = startColumn - 1;
      return j >= start && j < start + length ? c : [];
    })
  );
}
  • In this case, when you put a custom function of =SUM(someKindOfFunction(A1:D1,2,2)) to a cell using your sample Spreadsheet, 5 is returned.

References:

Upvotes: 4

Related Questions