| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Last n Rows in Expanding Table
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting Last n Rows in Expanding Table
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))) -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ups.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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting Last n Rows in Expanding Table
Or better
=SUM(OFFSET($F$1,COUNT($F$1:$F$1000),,-4,)) -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ups.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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting Last n Rows in Expanding Table
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 ups.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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting Last n Rows in Expanding Table
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 ups.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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting Last n Rows in Expanding Table
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 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 ups.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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting Last n Rows in Expanding Table
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting Last n Rows in Expanding Table
|
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting Last n Rows in Expanding Table
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table - Is counting all rows | Excel Discussion (Misc queries) | |||
| Expanding / Collapsing rows | Excel Discussion (Misc queries) | |||
| Excel formula in an expanding table | Excel Discussion (Misc queries) | |||
| Expanding and collapsing rows | Excel Discussion (Misc queries) | |||
| Expanding/Collapsing Rows? | Excel Worksheet Functions | |||
Linear Mode
