CODEWITHSUNDEEP
excelgoogle-sheetsarray-formulas
AlexanderP
AlexanderP

Reputation: 171

How are Google Sheets array formulas different from Excel (count until sum reached)

I'd like to know which column is the last column where the sum of the values of the row is smaller or equal to a given value. (Count the columns until a sum is reached.)

In Microsoft Excel the following array formula works just fine:

{=MATCH(7;SUBTOTAL(9;OFFSET(C1;;;1;COLUMN(C1:G1)-COLUMN(C1)+1));1)}

But Google Sheets always returns 1 as an answer:

=ARRAYFORMULA(MATCH(7;SUBTOTAL(9;OFFSET(C1;;;1;COLUMN(C1:G1)-COLUMN(C1)+1));1))

Is there some difference between Excel and Google Sheets array formulas that I'm missing?

If there is a difference is it documented somewhere?

Is there another way to implement this in Google Sheets (preferably without custom functions)?

Link to sample spreadsheet.

Upvotes: 2

Views: 2293

Answers (1)

AdamL
AdamL

Reputation: 24629

Is there some difference between Excel and Google Spreadsheet array formulas that I'm missing?

The difference is how specific functions are supported in array formulae. In this case, you're out of luck on two counts: OFFSET can't be iterated over an array (ie it can't produce an "array of arrays" as it can in Excel), and the second argument of SUBTOTAL can't be iterated either; in Sheets, it must be an explicit range.

If there is a difference is it documented somewhere?

No, not that I know of.

Is there another way to implement this in Google Spreadsheet (preferably without custom functions)?

=ArrayFormula(MATCH(7;SUMIF(COLUMN(C1:G1);"<="&COLUMN(C1:G1);C1:G1)))

Upvotes: 3

Related Questions