offset SUM ranges

[Rainmanne]

I need to offset a number of SUM ranges, which look something like this:

=-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:

=-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]

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)

 

[Rainmanne]

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]

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]

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)

 

[Rainmanne]

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.