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!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
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

New Member
Joined
Nov 1, 2019
Messages
3
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

New Member
Joined
Nov 1, 2019
Messages
3
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
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>
 

Forum statistics

Threads
1,081,840
Messages
5,361,618
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top