offset SUM ranges

Rainmanne

New Member
Joined
Mar 10, 2016
Messages
42
I need to offset a number of SUM ranges, which look something like this:
Code:
=-SUM(FS!C79:C80,FS!C82:C85,-FS!C81)
I can obviously offset each reference in the formula, something I tried with a simpler range:
Code:
=-SUM(,OFFSET(FS!B76,,FS!$B126):OFFSET(FS!B77,,FS!$B126))
but it is a bit messy and very difficult to manage with longer ranges. I wonder if there is a more elegant solution for that?
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
You could try nested named ranges

Range1 : Refers to = OFFSET(FS!B76,0,FS!$B126)
Range2 : Refers to = OFFSET(FS!B77,0,FS!$B126)
SumRange: Refers to = Range1:Range2

=-SUM(SumRange)
 
Last edited:

Rainmanne

New Member
Joined
Mar 10, 2016
Messages
42
You could try nested named ranges

Range1 : Refers to = OFFSET(FS!B76,0,FS!$B126)
Range2 : Refers to = OFFSET(FS!B77,0,FS!$B126)
SumRange: Refers to = Range1:Range2

=-SUM(SumRange)
Not sure if I will be able to fill the formula right in this case. The offset number should be dynamic.
 

Rainmanne

New Member
Joined
Mar 10, 2016
Messages
42
I just would like to explain what I am trying to do.

I have a table with annual data which can cover from 3 to 5 years. Then I would like to summarise the data from the table in another table but only for the three last years. By summarising I mean to sum up some rows to have 1 line instead of say 5 for some cases. So I was trying to use the OFFSET function to offset the columns depending on a number of total columns for the first column of the summary table and then fill the formula to the right. However, while it is possible technically, the formulas become to long (I have some other conditions and use IF structure). Thus, I am trying to find an easier solution for that.
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
It sounds like you need SUMIFS rather than SUM/OFFSET. If the years are in separate columns you would need a separate SUMIFS for each column and you can control whether the year is included or not using a cell value somewhere.
it might be a long formula as you've got say 5 separate SUMIFS but as long as the SUMIFS is simple that doesn't matter, it'd be less complex to maintain than the equivalent SUM/OFFSETs.

If it needs to be dynamic, you can use OFFSET in it's longer form and SUMPRODUCT with it's vector form
.
OFFSET(Anchorcell,Rows down, cols across,number of rows in range, number of columns in range) where Number of columns can be determined by a cell value you input and can be negative so to pick up the last x years you'd base the anchor in the rightmost column and use a -ve number for the cols across value and the +ve number for the range width.
So if you've 1 col of text and 5 cols of data base it in col F and use OFFSET($F1,1,-H2+1,100,H2)
where data is in rows 2 - row 100, and H2 has the number of years to include. If you put in 3 in effect this would be OFFSET($F1,1,-2,100,3) i.e. OFFSET($D1,1,0,100,3) which covers 3 columns D-F

=-SUMPRODUCT(OFFSET(FS!F1,1,-{Number of years of data to include}+1 ,{Number of rows in table1},{Number of years of data to include}),--(FS!A2:A100={label in summary report})
You might need to change -- to 1* if there's blanks or zeroes (I forget which doesn't work with which)
 
Last edited:

Rainmanne

New Member
Joined
Mar 10, 2016
Messages
42
Thanks a lot.

I have decided to do it with SUM(INDEX) and SUM(INDEX/MATCH). It seems to work but if there are issues I will try the method you've suggested.
 
Last edited:

Forum statistics

Threads
1,082,269
Messages
5,364,150
Members
400,783
Latest member
sambills

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