Trying to sum items due per week, with variable number of items/day and some days repeated

I have a spreadsheet showing package due-dates (Sheet1, col. A) and then the number of items in each package (Sheet1, col. B). I would like to sum for each week on Sheet2 the total number of items due that week. For example, if for the week of 8-Jul-2013 there are three packages due on 9-July-2013, 9-July-2013, and 12-July-2013, with 3,4, and 8 items respectively, then I'd want Sheet2 Column B to show that there are 15 total items due in that week.

I at first thought to try vlookup, but I am not sure how to use that with possible multiple returns, and also a range of dates (any date in the specified week) rather than a fixed value. I have also looked at array formulas but I am out to sea on how I would adapt what I found on Google to account for the variable date range.

Example.xls

Can you just use sum if ?

=SUMIF(Sheet1!$A$2:$A$217,Sheet2!A2,Sheet1!$B$1:$B$217) and drag it down

See the attached file.

I used an public function to determine the week.

I also added the month and year.

After that I made an pivot table.

Notice: if there is an addition in the data-cell you get an failure (you also see this failure in the pivot table).

Please Login or Register  to view this content.

Always good to add to whom your responding.

You got several solutions, so which one solved your problem.

Thanks for the reply.

Glad I could help.