Summation range depends from cell (MS Excel)

0
votes

Suppose that I have Excel workbook with two sheets, lets name them 'sheet one' and 'sheet two'. I want to perform a summation in range from cell 'sheet two'!R125C('sheet one'!R2C4+6) to cell 'sheet two'!R137C('sheet one'!R2C4+6) in R1C1 notation. So my goal is to have a sum which columns range depends from given cell.

I tried

=SUM('sheet two'!ADDRESS(125;R2C4+6;1;0):'sheet two'!ADDRESS(137;R2C4+6;1;0))

but it doesn't work (I can't see my mistake). Maybe it is also possible to perform this task using INDIRECT function. So, what is the proper way to evaluate sum which range depends from a value in a cell?

Any help will be very appreciative.

UPDATE: I also tried

=SUM(INDIRECT("'sheet two'!R125C"&R2C4+6&":'sheet two'!R137C"&R2C4+6;FALSE))

and it returns me an #REF! error.

1
excelworksheet-functionworksheet
Excel function not use character ";", only use character "," So you can change ";" to "," and try again – hung34k
@hung34k, I have successfully used ";" as an argument separator for all my previous evaluations with functions. Just to be more precise, it depends from your system settings and is independent from Excel. – Hasek
I try this function and have result: =SUM(INDIRECT(ADDRESS(125,R2C4+6,1,0,"sheet two"),FALSE):INDIRECT(ADDRESS(137,R2C4+6,1,0,"sheet two"),FALSE)) – hung34k

1 Answers

0
votes

I have successfully solved my problem with the help of different approach. I just searched through the head of table in my 'sheet two' with MATCH function and returned column index, then substituted it into INDIRECT and SUM functions.

The full working function is

=SUM(INDIRECT("'sheet two'!R125C"&MATCH("Total number";'sheet two'!R2;0);FALSE):INDIRECT("'sheet two'!R137C"&MATCH("Total number";'sheet two'!R2;0);FALSE))