Referencing two variables for a SUMPRODUCT

[jardenp]

I'm trying to figure out how to use SUMPRODUCT with one of the variables being within a range, not a direct match. I know how to do this when trying to match two variables, but the date range is tripping me up. I'm trying to figure out the formula that goes in C2 of Book1-Sheet1.

I have a list of Unit Numbers, Transaction Dates, and Amounts that is roughly 160,000 rows with about 400 unit nuumbers. My goal is to summarize the amounts for units in a given period of time from their in-service date. The periods are years out from the in-service date, with the years being individually accounted for. For example, if unit 9828 was in-service on 1/5/2005, then year one would be 1/5/2005 - 1/4/2006 and year 2 would be 1/5/2006 - 1/4/2007, etc.

Book1-Sheet1
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Unit[/TD]
[TD]In-Service Date[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]1/5/2005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The values in C1:G1 are numbers and I was planning on using them in the formula. So the formulas would be:

C2 = sum of all transactions amounts in Book2-Sheet1 with (Unit = A2) and (TransDate is [>= (MM of B2)/(DD of B2)/(Year of B2 + (C1 - 1))] AND [< (MM of B2)/(DD of B2)/(Year of B2 + C1)]
D2 = sum of all transactions amounts in Book2-Sheet1 with (Unit = A2) and (TransDate is [>= (MM of B2)/(DD of B2)/(Year of B2 + (D1 - 1))] AND [< (MM of B2)/(DD of B2)/(Year of B2 + D1)]
etc.

So, the data set:

Book2-Sheet1
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Unit[/TD]
[TD]TransDate[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]2/5/2005[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]3/5/2005[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]2/5/2006[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]2/5/2007[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]3/5/2007[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]4/5/2007[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]2/5/2008[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]2/5/2009[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

Would yield:

Book1-Sheet1
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Unit[/TD]
[TD]In-Service Date[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]9828[/TD]
[TD]1/5/2005[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]300[/TD]
[TD]100[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

I'm looking for the formula for C2 and I can figure the rest from there.

Thanks!

Josh in IN

 

[jardenp]

OK. I think I found my problem: I was using too large of numbers for the lookup ranges. I wanted to leave my options open so I used, for example, D2:D1000000. I changed things to D2:200000 and it now works. Here I was thinking I needed a different approach to SUMPRODUCT.