offset cell reference

belladz78

New Member
Joined
Nov 1, 2019
Messages
3
Hi! Wondering what the best way to sum a number of columns based on a particular cell which is dynamic.

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>

<tbody>
</tbody>

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.
JanFebMarAprMayJunJul
Customer 1 $ 100.00 $ 80.00 $ 75.00 $ 125.00 $ 150.00 $ 110.00 $ 25.00
Cell referenceResult
Mar $ 255.00

<tbody>
</tbody>
=SUM(OFFSET(E3,0,-3,1,3))

<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>
Thanks!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top