offset cell reference

[belladz78]

Hi! Wondering what the best way to sum a number of columns based on a particular cell which is dynamic. [TABLE="width: 104"]
<tbody>[TR]
[TD="align: left"]
I can use this formula when E3 is static, but wondering how to get E3 to be dynamic based off a cell value

=SUM(OFFSET(E3,0,-3,1,3))

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

Example below, the formula manually has E3 which is location of Mar in the data. But Mar will change by each instance so i want to be able to have the cell reference change based on a formula.
[TABLE="width: 577"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD] $ 100.00[/TD]
[TD] $ 80.00[/TD]
[TD] $ 75.00[/TD]
[TD] $ 125.00[/TD]
[TD] $ 150.00[/TD]
[TD] $ 110.00[/TD]
[TD] $ 25.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cell reference[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD] $ 255.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]

<tbody>
</tbody>

[/TD]
[TD="colspan: 2"] =SUM(OFFSET(E3,0,-3,1,3)) [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

Perhaps I haven't understood exactly what you are after, but I would try to avoid the volatile function OFFSET.

See if this non-volatile option is any use. If not, perhaps you can clarify further.

<b>SUm variable amount</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:99px;" /><col style="width:69px;" /><col style="width:62px;" /><col style="width:62px;" /><col style="width:69px;" /><col style="width:69px;" /><col style="width:69px;" /><col style="width:62px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Jan</td><td style="font-size:10pt; text-align:right; ">Feb</td><td style="font-size:10pt; text-align:right; ">Mar</td><td style="font-size:10pt; text-align:right; ">Apr</td><td style="font-size:10pt; text-align:right; ">May</td><td style="font-size:10pt; text-align:right; ">Jun</td><td style="font-size:10pt; text-align:right; ">Jul</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Customer 1</td><td style="font-size:10pt; text-align:right; ">$100.00 </td><td style="font-size:10pt; text-align:right; ">$80.00 </td><td style="font-size:10pt; text-align:right; ">$75.00 </td><td style="font-size:10pt; text-align:right; ">$125.00 </td><td style="font-size:10pt; text-align:right; ">$150.00 </td><td style="font-size:10pt; text-align:right; ">$110.00 </td><td style="font-size:10pt; text-align:right; ">$25.00 </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">Cell reference</td><td style="font-size:10pt; ">Result</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; ">Mar</td><td style="font-size:10pt; text-align:right; ">$255.00 </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B7</td><td >=SUM(B3:INDEX<span style=' color:008000; '>(B3:H3,MATCH<span style=' color:#0000ff; '>(A7,$B$2:$H$2,0)</span>)</span>)</td></tr></table></td></tr></table>

 

[belladz78]

Hi! Thanks for the reply. i am using the offset since the starting column will not always be B3 / Jan in this example. In this example, if I choose Jun vs Mar i still only want the 3 columns prior to Jun. The number of columns to sum remains constant in this example at 3 prior to cell reference. The cell reference E3 in the offset formula is what I want to change based on the input cell criteria.

 

[belladz78]

I figured it out. In case anyone else runs into this, I used INDEX/MATCH for the cell reference. Manual cell reference formula: =SUM(OFFSET(E3,0,-3,1,3)) using dynamic range for E3: =SUM(OFFSET(INDEX(B2:H2,MATCH(A6,$B$1:$H$1,0)),0,-3,1,3)). In this example A6 is the cell lookup value, to be looked up in B1:H1

 

[Peter_SSs]

The number of columns to sum remains constant in this example at 3 prior to cell reference.

If you don't have masses of these formulas on your sheet, using OFFSET shouldn't be a problem. However, you can still avoid the volatility associated with OFFSET as follows.

<b>Sum variable amount</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:99px;" /><col style="width:69px;" /><col style="width:62px;" /><col style="width:62px;" /><col style="width:69px;" /><col style="width:69px;" /><col style="width:69px;" /><col style="width:62px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Jan</td><td style="font-size:10pt; text-align:right; ">Feb</td><td style="font-size:10pt; text-align:right; ">Mar</td><td style="font-size:10pt; text-align:right; ">Apr</td><td style="font-size:10pt; text-align:right; ">May</td><td style="font-size:10pt; text-align:right; ">Jun</td><td style="font-size:10pt; text-align:right; ">Jul</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Customer 1</td><td style="font-size:10pt; text-align:right; ">$100.00 </td><td style="font-size:10pt; text-align:right; ">$80.00 </td><td style="font-size:10pt; text-align:right; ">$75.00 </td><td style="font-size:10pt; text-align:right; ">$125.00 </td><td style="font-size:10pt; text-align:right; ">$150.00 </td><td style="font-size:10pt; text-align:right; ">$110.00 </td><td style="font-size:10pt; text-align:right; ">$25.00 </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Cell reference</td><td style="font-size:10pt; text-align:right; ">Result</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">Mar</td><td style="font-size:10pt; text-align:right; ">$255.00 </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B6</td><td >=SUM(INDEX<span style=' color:008000; '>(B2:H2,MATCH<span style=' color:#0000ff; '>(A6,$B$1:$H$1,0)</span>-2)</span>:INDEX<span style=' color:008000; '>(B2:H2,MATCH<span style=' color:#0000ff; '>(A6,$B$1:$H$1,0)</span>)</span>)</td></tr></table></td></tr></table>