How can I use SUM, OFFSET, and VLOOKUP all at once?

[MSuits]

I am having trouble creating a formula to sum up numbers on a separate worksheet based on the values in my first sheet. The problem I am having is that sheet1 is based in months, but I need weekly values, which I have in sheet2. Sometimes I need the sum of values from 3 or 5 weeks worth of data, which isn't compatible with the monthly numbers. I am very familiar with vlookups, but I am unsure of how to use the offset formula (if that is the simplest way) in this situation.


I have tried to use =sum(offset(vlookup(A1,'sheet2'A:F,2,false),0,4,,4)) but it doesn't seem to work.


Here is a simplified version of some data I am using:


Sheet 1

Item# Weeks

782 4

609 5

936 3


Sheet 2

Item # Week1 Week2 Week3 Week4 Week5

782 2 4 5 3 2

609 5 3 6 2 4

936 2 3 5 4 1


So, for item 782 I need to add up the first 4 weeks, for 609 I need the first 5, and for 936 I need the first 3.


I would really appreciate any suggestions and tips. Thanks

 

[keymaster]

Interesting problem


Here is how I would solve it.


Assuming data in Sheet 2 A2:Z100 (first row for headers)

and assuming data in Sheet 1 is in A2:B10


in Sheet 1, wherever you want the result, write

=sum(offset(Sheet2!$A$2,match(A2,Sheet2!$A$2:$A$100,0)-1,0,1,B2))


Now the formula works fine as long as values in Sheet1 are available in Sheet2. Otherwise it throws an error, in which case, you can use IFERROR or ISERROR to handle them.