Problem with OFFSET in the sum range of a SUMIF formula

**wanwan79** · 08-13-2017, 12:54 PM

Hi

This is my first post here - so sorry if I am doing anything wrong!

I have been grappling with a SUMIF formula with an offset. I will try and explain the purpose of it:

- We run a financial year from July to June
- Each month I want to be able to check the actual performance in a month and the financial year to date performance
- The data I am interrogating is exported out of oracle and then pasted in my tracking sheet
- The issue with the export that is usable is that it always spits out the last 12 months - regardless of the financial year
- To allow me to track correctly I am trying to use an offset value that is derived from a vlookup in a config page - where July is 1, August is 2, September is 3 and so on
- Each month I change the month in one cell and then have the adjacent cell generate the offset
- I am then referencing the numeric value created +1 from that as the the column offset for the sum range value in a SUMIF formula
- I am then using the same reference cell for the column width in the offset
- What I am trying to achieve is to have the sum range capture an extra month each time i increment the reporting month
- The purpose of using a summit is that I have multiple cost centres, which have different cost lines (column A), so I am trying to cater for the variance by using a sumif to check for cost line in the Data sheet

I'm sorry, but I couldn't get the screenshot working, so I have attached two files.

'Data' is where my sum range is - in the sheet this is a tab called 'Actual - 22222'

'Tracker' is the sheet I have the formula in

The formula in 'Tracker' cell J8 is:

=IFERROR(SUMIF('Actual - 22222'!A:A,'22222'!A9, (OFFSET('Actual - 22222'!N:N,0,-'1.Comparison to Budget'!$C$3+1,1,'1.Comparison to Budget'!$C$3))),"")

For reference, the ''1.Comparison to Budget'!$C$3' is the cell with the numeric offset mentioned above. at the moment this value is '4' (I overtyped it for the sake of testing).

What is actually happening with this formula is that it is picking up the data from column K in the data range.. rather than all 4 columns

Any help greatly appreciated!

Please let me know if i haven't provided enough information.

Thanks!

Sam

**JBeaucaire** · 08-13-2017, 01:17 PM

I haven't gotten into the nitty gritty of your question, but I have a general suggestion to greatly ease your life when dealing with fiscal years that start midyear.

In a nutshell, add a column to your exported data to flag the pertinent data. Then ANY function you apply to the data table can work in its simplest form.

1) I would have a PERIODS sheet with a two-column table for looking up FISCAL YEAR ID.

Please Login or Register  to view this content.

2) Then I'd add a simple VLOOKUP into the data in a new column, if the transaction date were in column A of the data sheet:
=VLOOKUP(A2, Periods!$A:$B, 2, 1)

Copy the formula own and you get the ID for each row of data. All the heavy lifting is now DONE.

You can now do simple SUMIF using the new ID to collate the data. I would actually use a simple PIVOT TABLE.

Instead of numerical IDs, it could even be descriptive text, any kind at all. Since the VLOOKUP brings in the values consistently the Pivot Table would offer that text as display values.

Your life goes back to being simple in the reporting world.

My 2 cents.