Vlookup Cell Reference For Sum Formula

[mco5044]

I have a spreadsheet where it shows sales in units per product on sheet A along with some other data.

On sheet B is my weekly report. I also have a graph that shows the sum of unit sold per week for the last 4 weeks

The data for that graph has a week start date and week end date cell and then the product name

I want to use a vlookup on the start date and end date to find the cell reference of the sales data. That way I can use a sum function to auto calculate the total sales for the full week

Sheet A
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]3/8/15[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]3/9/15[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]3/10/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3/11/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3/12/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3/13/15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/14/15[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


Sheet B
[TABLE="width: 500"]
<tbody>[TR]
[TD]Week Start[/TD]
[TD]Week End[/TD]
[TD]Product Sales[/TD]
[/TR]
[TR]
[TD]3/8/15[/TD]
[TD]3/14/15[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

So product sales formula would vlookup based on the date in week start to find cell reference b2 and then same thing to find cell reference b8. The result would be Sum(B2:B8)

I can do all the vlookups, but Im struggling with how to do the cell references. I've looked into Index, Match, Address, Cell functions and can't put it together

Thanks!

 

[svendiamond]

Use this:

=SUMIFS(B:B,A:A,">="&$D$1,A:A,"<="&$E$1)

Where D1 is your start date and E1 is your end date. If your start date and end date are on a different sheet just change D1 and E1 to whichever cells you are using. And I know you shouldn't refer to an entire column like "A:A" but I don't know how far your data reaches so you can adjust that part, too.

 

[mco5044]

I Love It!

Thanks so much for your help. I was in so deep that the only way I could think of a solution was vlookups when you're right, the sumifs function works perfectly here.

Really appreciate your help on this one