Counting Last n Rows in Expanding Table

  • Thread starter Thread starter jmacvicar
  • Start date Start date
J

jmacvicar

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
 
one way where 999999 is larger than any number possible to sum last -3=last
4 rows
=SUM(INDIRECT("f"&MATCH(999999,F:F)-3&":f"&MATCH(999999,F:F)))
 
Or better
=SUM(OFFSET($F$1,COUNT($F$1:$F$1000),,-4,))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software





- Show quoted text -
Click to expand...

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






- Show quoted text -
Click to expand...

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
 
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
(e-mail address removed)
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






- Show quoted text -
Click to expand...

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




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 -
Click to expand...

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

Jeff
 
Send me a workbook to the address below


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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




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 -
Click to expand...

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

Jeff
 
Use a defined name and refer to that

=OFFSET(Sheet3!$G$1,MATCH("zzzzz",Sheet3!$G:$G)-20,0,20)
 
Back
Top