formula to identify week number based on date ranges and add values

[Lmendez]

Hello,

I have the following data:

Column A = Date
Column B = Reservations made per day
for ex:

A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15

Then I have a look up table where based on the date ranges it assigns a week number.

Week DATE Range 1 Date Range 2
7 18-Feb-11 24-Feb-11
8 25-Feb-11 03-Mar-11
9 04-Mar-11 10-Mar-11
10 11-Mar-11 17-Mar-11
11 18-Mar-11 24-Mar-11
12 25-Mar-11 31-Mar-11
13 01-Apr-11 07-Apr-11
14 08-Apr-11 14-Apr-11
15 15-Apr-11 21-Apr-11
16 22-Apr-11 28-Apr-11

I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.

Thank you for your help.

[thameem127]

Upload your file.

[AL1976]

hi,
does the attached file help?
rgds,
AL

week.xlsx

[bebo021999]

Please Login or Register  to view this content.

[Lmendez]

I have attached the file.

[Lmendez]

Thank you it worked!

[bebo021999]

Try in K2:

Please Login or Register  to view this content.

[thameem127]

Check attached file.

[Pepe Le Mokko]

And a Pivot Table will do it for you...

[Lmendez]

Thank you it worked. Would you be able to explain me a little what is this formula doing? Maybe by parts if at all possible
I can see that it works, I just don't understand what I did. LOL
Why do we multiply one piece by the other?

[bebo021999]

At least, can you let us know which solution work?
I am happy to explain but i am not sure if it works

[Lmendez]

I apologize, I thought it could mark whcih post I was replying to.

This solution worked:
SUMPRODUCT(($E$2:$E$44>=VLOOKUP(J2,$A$1:$C$11,2,0))*($E$2:$E$44<=VLOOKUP(J2,$A$1:$C$11,3,0))*$F$2:$F$44)