I *think* I should be using a sumif function with an offset.
I am trying to sum the units for 3 full weeks prior to the week entered
by a user, and this is working well:

M3=SUM(OFFSET(INDEX($A$1:$K$1,MATCH(M2,$A$1:$K$1)) ,MATCH(M1,$A:$A,0)-1,IF(ISNUMBER(MATCH(M2,$A$1:$K$1,0)),-2,-1),1,3))

where B1:K1 are week ending dates (2/3, 2/10...4/7)
A2:A8 are regions (Americas - West, EMEA - East, Japan, EMEA - West,
Americas - East, APAC, UK...)
B2:K8 contain the weekly unit numbers.

The user enters the region in M2, and enters the date in M3.

This formula works well to sum the 3 weeks prior to the date entered
for the region entered (if user enters specifically 'Americas - West'
for example).

What I would like to do but I am not sure how, is to modify the formula
so in M1 the user could just enter "Americas" and it would be able to
sum the 3 weeks prior for each of 'Americas - West' and also 'Americas
- East' rows, for example.
Is there an easy way to do this based on the formula above, or do I
need to start over? Any suggestions would be great.
Thanks-


--
hizzle
------------------------------------------------------------------------
hizzle's Profile: http://www.excelforum.com/member.php...o&userid=29370
View this thread: http://www.excelforum.com/showthread...hreadid=509691