Using sumproduct to sum values associated with years

I have an interesting problem I cant figure out! I have a file which might make it easier but I will try to explain.

I have 4 worksheets in one workbook. Datasheet, Homeloans, YR2, Table.

On Datasheet in M2 I have a Date formatted as d-mmm-yy

On Homeloans I have Dates from A1:L1 which are derived from the date on Datasheet =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+0,1) that progress up to =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+11,1) in L1
In A2:L2 I have values entered

On YR2 I have the same setup. The date system is setup to look like a fiscal year instead of a calendar year.

On the Table sheet from B1:Y1 I have dates. B1 uses the formula =IF(Datasheet!M2<>"",TEXT(Datasheet!M2,"d-mmm-yy"),"Jan") B2 uses =IF(OR(B$1="",B$1=""),"",TEXT(EDATE(B$1,1),"d-mmm-yy")) and is propagated to Y1.

In B2:Y2 I am using this formula =SUMPRODUCT(--(TEXT(Homeloans!$A$1:$L$1,"d-mmm-yy")=B$1),Homeloans!$A$2:$L$2) with it changing respectively to Y2

This is the tricky part.

On Table, Cell C18 I have a data validation list setup to select a year number

On Table, E18 I have formula =SUMPRODUCT(--(RIGHT(TEXT($B$1:$Y$1,"d-mmm-yy"),1)+0=$C$18),$B$2:$Y$2)

Here is the problem.

If I set the date on Datasheet to 1/11/2011 and I select year one on C18 on the Table sheet, I get the correct sum on E18.

If I change the date on Datasheet to 1/11/2012 it flips the value in E18 to zero. This is the weird part. If I change the value in C18 to 2 for second year, then it gives me the correct sum but it should be for year 1. So I played with the formula
=SUMPRODUCT(--(RIGHT(TEXT($B$1:$Y$1,"d-mmm-yy"),1)+0=$C$18),$B$2:$Y$2) and found that if I change the +0 part to +-1 then select year one again, it gives me the correct sum.

It sort of does what I need it to do but the problem is for every date set I will have to go in and change that value up and down the number line according to the date on Datasheet and that just isnt going to work. It need to be dynamic and pull values based on the year the values show up in.

So if a payment is entered for example on 1/11/11 as the first payment and the second payment on 1/12/11, and I want to know the totals for the first year of the loan, it should only give me the value for those two dates in E18.

Any help in resolving this would be greatly appreciated. Im running out of forehead to bang against the wall!

Regards,
D

Here it is. Thank you!

Quote from Tom.Jones

I modified almost all formulas in your file.
See if it is what you want, in attach

riskier4ra test.xlsx

P.S. Why you change date in text then change back?

Hi Tom,

The date change was probably when I was testing it while waiting for some help. Sorry for any confusion. Yes that is what I want to happen but would prefer to be able to select by year number of the loan instead of an actual year (if possible), and it be relative to the dates in the table from least to greatest, which should rely on the date on datasheet. So for example. I started a loan in 2002, and its 2018, I want to be able to select the year I am in of the loan which would be 16. That way I have a generic data validation list that is based on the maximum number of years of a standard homeloan which is 30 here. This will prevent me from having to update the name range to include years that a loan may be in. Not sure if that is possible with excel. I understand why you made the changes you did, and why the DVL is by year now instead of number, but I do not know if excel is limited in that capacity or if there is another way to make what I need to happen happen. I have to admit I am not an excel guy. Systems administration, Im good. Knowing the full extent of Excel's capabilities, not so much. I do appreciate your help and time, I just need it to work with a number and not a specific year, if possible.

Quote from Tom.Jones

If I understand you...
change back 2001 to 1, 2002 to 2 and so on and change formula in E18 with:

=SUMPRODUCT((YEAR($B$1:$Y$1)=--("200"&$C$18))*$B$2:$Y$2)

I made the changes you suggested and E18 just remained zero.

Quote from riskier4ra

I made the changes you suggested and E18 just remained zero.

I messed with it some more and I got it to work with your updated formula. Not sure why it didnt work the first time.

That way just does what it use to do without having to change +0. Your first proposal works fine. I will just have to make the Data Validation List Range a lot larger.

Your second proposal =SUMPRODUCT((YEAR($B$1:$Y$1)=--("200"&$C$18))*$B$2:$Y$2), simply looks at the first three characters of the year and adds the value in C18 to it for it to find the values to sum. So what happens is if I change the year to 2002 on the datasheet, and I select 1 in C18 I get nothing, but If I select 2, then I get the values for 2002, however the number 2 that I select in C18 is supposed to represent the number of years into the loan so I should be able to select 1 and get the values for year 2002.

To further explain, lets say you started a loan in 2006, but you are in 2018 and you want to pull the expenses for 2018, I would like to select the value 12 because its 12 years into the loan. This way I only need to use 1-30 for the data validation list name range.