Trying to use sumif and offset functions

[hizzle]

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-

 

[Biff]

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))

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

 

[Biff]

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

 

[hizzle]

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!

 

[Biff]

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]

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