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

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

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

Thanks- boy this is getting close!! :)

Regarding the table layout, unfortunately it can't be modified. The
worse news is that my example is just a very small version. The real
worksheet is thousands of rows and column A contains regions like:
Americas - South, Americas - California, New York (Americas), Texas
(Americas), and not all of the “Americas” are grouped in adjacent rows!
Basically the region could be entitled anything, the only thing I know
for sure is that the string "Americas" (as an example) is somewhere in
the cell. So there may be 60 cells with "Americas" in it somewhere, 50
for EMEA, etc. spread throughout thousands of non-adjacent cells in
column A

oops - my bad I typed in the reference incorrectly.
Region is in M1 and date is in M2 - thanks for the catch!

I modified the formula as you suggested, but I can only get it to work
if the "Americas" cells are adjacent. If they are spread out, it knows
how many rows to pick up but it isn’t able to “find” each one and
instead just starts summing from the first cell with “Americas” and
then the rows/cells below it.

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)),-3,-2),COUNTIF(A$2:A$8,"*"&M1&"*"),3))

Any thoughts? thanks again!! appreciated!


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

Ok, try this:

You'd have to enter a start date and an end date:

M1 = Americas (or whatever)
M2 = start date
M3 = end date

=SUMPRODUCT((ISNUMBER(SEARCH(M1,A2:A10)))*INDEX(B2 :K10,,MATCH(M2,B1:K1,0)):INDEX(B2:K10,,MATCH(M3,B1 :K1,0)))

Biff

"hizzle" wrote in
message ...

Thanks- boy this is getting close!! :)

Regarding the table layout, unfortunately it can't be modified. The
worse news is that my example is just a very small version. The real
worksheet is thousands of rows and column A contains regions like:
Americas - South, Americas - California, New York (Americas), Texas
(Americas), and not all of the "Americas" are grouped in adjacent rows!
Basically the region could be entitled anything, the only thing I know
for sure is that the string "Americas" (as an example) is somewhere in
the cell. So there may be 60 cells with "Americas" in it somewhere, 50
for EMEA, etc. spread throughout thousands of non-adjacent cells in
column A

oops - my bad I typed in the reference incorrectly.
Region is in M1 and date is in M2 - thanks for the catch!

I modified the formula as you suggested, but I can only get it to work
if the "Americas" cells are adjacent. If they are spread out, it knows
how many rows to pick up but it isn't able to "find" each one and
instead just starts summing from the first cell with "Americas" and
then the rows/cells below it.

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)),-3,-2),COUNTIF(A$2:A$8,"*"&M1&"*"),3))

Any thoughts? thanks again!! appreciated!


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

Thanks biff. Outstanding!
I just had to change the formula so instead of using a end date, I just
convert it to X number of weeks after. This is great!!! Really
appreciated -
:D


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