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