Use a defined name and refer to that

=OFFSET(Sheet3!$G$1,MATCH("zzzzz",Sheet3!$G:$G)-20,0,20)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Send me a workbook to the address below


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
oups.com...
On Oct 27, 11:34 am, "Don Guillett" wrote:
Without looking too closely, you are NOT trying to match chev but some
letters that can't exist. Go back to "zzzzzzzzz"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
om wrote in message

ups.com...
On Oct 27, 11:01 am, "Don Guillett" wrote:





No you don't cuz sumproduct doesn't work on entire columns.
Modify this to suit and substitute the *1 for your second condition.
=SUMPRODUCT((INDIRECT("h"&MATCH("zzzzzz",H:H)-3&":h"&MATCH("zzzzzzzzz",H:H)**)="chev")*1)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
om wrote in message

oups.com...

On Oct 27, 9:57 am, "Don Guillett" wrote:
Or better
=SUM(OFFSET($F$1,COUNT($F$1:$F$1000),,-4,))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
om wrote in
message

roups.com...

I have a table that is constanty being updated from a connection to
a
text file. Right now I'm using a SUMPRODUCT formula to summarize
the
entire table based on certain values in the data. I want to change
this so I'm only looking at the last n rows. What would be the
best
way to accomplish this ?

Thank-you

Jeff- Hide quoted text -

- Show quoted text -

I could a bit more help in where to put this in relation to my
existing formula. Here is what I currently have:

=SUMPRODUCT((Sheet3!K:K="CHEV")*(Sheet3!G:G="AWD") )

Thanks

Jeff- Hide quoted text -

- Show quoted text -

Here is how I have entered the formula but obviously I don't have
something correct.

=SUMPRODUCT((INDIRECT("k"&MATCH("CHEV",Sheet3!
K:K))-3&":k"&MATCH(("CHEV",Sheet3!
K:K))="CHEV"))*(("g"&MATCH("AWD",Sheet3!
G:G))-3&":g"&MATCH(("AWD",Sheet3!G:G))="AWD"))

Thanks

Jeff- Hide quoted text -

- Show quoted text -

I can't get this to work. Thanks for your help though.

Jeff