Help with SumIf formula to change range based on input criteria

[nwd9s]

Hi all-

I am looking for your expertise regarding help creating a dynamic sumif formula that could change which columns it sums based on input criteria.

The criteria is a date that I want to set it to, so say October 2011 is column G and December 2014 is column AD. If the user selects December 2014 I want the sumif to sum column AD.

There is one other twist to this request. The data set (i.e. the set of data relating to the months Column G and Column AD) will be growing, more columns as more dates pass and more rows as moer data is added. Is there a way to do this with offsets and match that it could work?\

Below is the current formula I have which is not working.

=sumif(OFFSET('Lease Rental Data'!A1,MATCH(B3,OFFSET('Lessee Data'!A:A,0,MATCH(I3,'Lessee Data'!3:3,0)-1),0)-1,MATCH(I3,'Lessee Data'!3:3,0)-1),B3,)

Thanks for your help. Even if you could help by creating a dummy formula, I could then adapt it to my model.

Thanks,
nwd9s

 

[Jonmo1]

This sounds doable...

What is the LEFTMOST Date for the sum, and which column is that?

And Is it basically 1 column for every month?
Oct2011 = G, Nov2011 = H, Dec2011 = I ?

Post a basic sumif formula based on the Leftmost(earliest) date to sum.

and in what cell will you put the desired date?

 

[nwd9s]

Hi Jonmo1-

Thanks for your help. Yes, each column represents a different month. It goes over 5 years into the future, so once that month passes, I update the database and it goes away. The leftmost column with a month in it is Column C with May-2011 and goes out all they way to Column BE currently, but could go out further to a max of 72 months (5 years plus current year).

So while each column represents a month, each row represents a car we lease. As we buy and sell cars, the data will change the number of rows.

The desired date is in cell I3, but in a different tab entitled 'Exposure Report' while the data base is in a tab called 'Net Book Value Data'

Hopefully this is clear.

Thanks.

 

[Jonmo1]

Do your columns C - BE have headers that indicate the date?

 

[Jonmo1]

Well, assuming your criteria for the sumif is in column A, and we have some unkown criteria...

Try this assuming there is a valid date in 'Exposure Report'!$I$3

=SUMIF(A:A,"somecriteria",OFFSET(C:C,0,DATEDIF(DATE(2011,5,1),'Exposure Report'!$I$3,"m")))

The red 2011 and 5 represent Year and Month of the first column (C)

 

[nwd9s]

Hi-

Yes the columns indicate the date. Thanks for the formula help, let me play with what you have given me and see if I can make it work. I will report back with my success or if I need more help tomorrow morning.

Thanks,
nwd9s

 

[Jonmo1]

Glad to help...

Good, it's better with Dates in the column headers...

=SUMIF(A:A,"somecriteria",OFFSET(C:C,0,MATCH('Exposure Report'!$I$3,C1:IV1)-1))

This way, you don't worry about putting the correct month/year in the formula.

 

[nwd9s]

Hi jonmo1-

Thanks again for your help. I was able to get a formula that worked, just had to tinker with it a little bit. The working formula ended up being:

=SUMIF(OFFSET('Lessee Data'!C:C,0,MATCH('Exposure Report'!I3,C3:IV3)-3),'Exposure Report'!B3,OFFSET('Net Book Value Data'!C:C,0,MATCH('Exposure Report'!I3,C3:IV3)-3))

I had twin data sheets, so in Net book value there are just numbers, and the names are in a different tab. But the cells match (i.e. cell F50 in lessee name and net book value would be the same lease).

Thanks again and I will let you know if i have any further questions.

Thanks,
nwd9s