Trying to use sumif and offset functions

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-

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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!

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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