CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
JOY
JOY

Reputation: 389

Calculate PRODUCT of a sub-range in a Google Sheets range

I have a range with Type for columns and Level for rows. I need to find the PRODUCT of a sub-range which is from Level 1 to the given Level of given Type.

So far I tried to find the position of the starting and ending cells of sub-range by using:

=CELL("address",INDEX(A1:N21,2,MATCH(R4,A1:N1,0)))

=CELL("address",index(A1:N21,R3+1,match(R4,A1:N1,0)))

Then I think about using CONCATENATE to give the range address but it returns 0.

Example sheet: https://docs.google.com/spreadsheets/d/1byIjDzHZE6s5N1PcN9yvSeC51bm9NVVC1tc1gjQQLHA/edit#gid=0

Upvotes: 0

Views: 301

Answers (2)

JOY
JOY

Reputation: 389

I found one solution by using the reference to the range:

=PRODUCT(INDIRECT(CELL("address",INDEX(A1:N21,2,MATCH(R4,A1:N1,0)))&":"&CELL("address",INDEX(A1:N21,R3+1,MATCH(R4,A1:N1,0)))))

However, it is quite long. I would like to have a better solution in the case.

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34370

You are right, you don't need such a long formula. Index returns a cell reference, so you can do something in the form of index(...startrow,startcol):index(...endrow,endcol) like this:

=PRODUCT(INDEX(A1:N21,2,MATCH(R4,A1:N1,0)):INDEX(A1:N21,R3+1,MATCH(R4,A1:N1,0)))

enter image description here

Even shorter is offset:

=PRODUCT(offset(A1,1,MATCH(R4,A1:N1,0)-1,R3,1))

Upvotes: 3

Related Questions