P.S.

You can still get the sum for the individual Americas and EMEA. In M1 you'd
just have to enter the full name: Americas - East.

I think I'd setup a drop down for cell M1 and have all the individual named
regions and then another for those regions that have sub-regions:

Americas
Americas - West
Americas - East
EMEA
EMEA - West
EMEA - East
Japan
APAC
UK

Biff

"Biff" wrote in message
...
Hi!

If you were to redesign your table such that:

Row2 = Americas - West
Row3 = Americas - East
Row4 = EMEA - East
Row5 = EMEA - West

Then all you need to do is change:

MATCH(M1,$A:$A,0)-1

Change to:

MATCH(M1&"*",$A:$A,0)-1

Then change the height argument of Offset to be calculated:

........)),-2,-1),1,3))

Change to:

........)),-2,-1),COUNTIF(A$2:A$8,M1&"*"),3))

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

Those input cells don't match what's in your formula! <g

Now, you'll be able to input the region as Americas. EMEA will do the
same!

Biff

"hizzle" wrote in
message ...

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