|
Need rolling summary of data
Have data on a spreadsheet:
Shipdate Quantity The data goes back 5 years, and is constantly updated with new shipments being added. Would like to have on another sheet a rolling summary of how many have been shipped within the last 12 months, the previous 12 months to that, etc, up to 5 years. So the summary would look something like this: Present to 12-Mos. 29 13-24 Mos. 55 25-36 Mos. 36 37-48 Mos. 55 49-60 Mos 39 TIA for any ideas on this. Pierre |
Need rolling summary of data
something like this where c1 & d1 contain your desired dates =sumproduct((month(a2:a22)=c1-2)*(month(a2:a22)<=d1-2)*b2:b22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Pierre" wrote in message oups.com... Have data on a spreadsheet: Shipdate Quantity The data goes back 5 years, and is constantly updated with new shipments being added. Would like to have on another sheet a rolling summary of how many have been shipped within the last 12 months, the previous 12 months to that, etc, up to 5 years. So the summary would look something like this: Present to 12-Mos. 29 13-24 Mos. 55 25-36 Mos. 36 37-48 Mos. 55 49-60 Mos 39 TIA for any ideas on this. Pierre |
Need rolling summary of data
On Oct 22, 11:47 am, "Don Guillett" wrote:
something like this where c1 & d1 contain your desired dates =sumproduct((month(a2:a22)=c1-2)*(month(a2:a22)<=d1-2)*b2:b22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Pierre" wrote in message oups.com... Have data on a spreadsheet: Shipdate Quantity The data goes back 5 years, and is constantly updated with new shipments being added. Would like to have on another sheet a rolling summary of how many have been shipped within the last 12 months, the previous 12 months to that, etc, up to 5 years. So the summary would look something like this: Present to 12-Mos. 29 13-24 Mos. 55 25-36 Mos. 36 37-48 Mos. 55 49-60 Mos 39 TIA for any ideas on this. Pierre- Hide quoted text - - Show quoted text - Don, thanks for your reply. Having a bit of a time though making it work. Col A is ship date. Col B is quantity shipped Looking for a calculation to give the quantity shipped within the last 12 months, lets say in D2. In D3 looking for the quantity shipped from months 13-24, and so forth. The suggestion to place in my desired dates dates in row 1 confuses me a tad. I'll have row labels in column C beginning on row 2 for the first group containing the shipments for the last 12 months. Thanks again for any second look. Pierre |
Need rolling summary of data
On Oct 22, 2:02 pm, "Don Guillett" wrote:
How about something like this? =sumproduct((month(a2:a22)month(today())-12)*(month(a2:a22)<=month(today()*)-0)*b2:b22) You could probably incorporate something using row()*12 instead -- Don Guillett Microsoft MVP Excel SalesAid Software "Pierre" wrote in message oups.com... On Oct 22, 11:47 am, "Don Guillett" wrote: something like this where c1 & d1 contain your desired dates =sumproduct((month(a2:a22)=c1-2)*(month(a2:a22)<=d1-2)*b2:b22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Pierre" wrote in message groups.com... Have data on a spreadsheet: Shipdate Quantity The data goes back 5 years, and is constantly updated with new shipments being added. Would like to have on another sheet a rolling summary of how many have been shipped within the last 12 months, the previous 12 months to that, etc, up to 5 years. So the summary would look something like this: Present to 12-Mos. 29 13-24 Mos. 55 25-36 Mos. 36 37-48 Mos. 55 49-60 Mos 39 TIA for any ideas on this. Pierre- Hide quoted text - - Show quoted text - Don, thanks for your reply. Having a bit of a time though making it work. Col A is ship date. Col B is quantity shipped Looking for a calculation to give the quantity shipped within the last 12 months, lets say in D2. In D3 looking for the quantity shipped from months 13-24, and so forth. The suggestion to place in my desired dates dates in row 1 confuses me a tad. I'll have row labels in column C beginning on row 2 for the first group containing the shipments for the last 12 months. Thanks again for any second look. Pierre- Hide quoted text - - Show quoted text - Don, it wants to work, but I'm not sure what it wants to do. What do I place in C1 and D1? It appears that I copy down your formula; I've changed it to absolute references with the exception of C1. Col C has my date/month ranges read as follows: Present to 12 13-24 25-36 37-48 Col D would have the corresponding number of shipments. Thanks again. Pierre |
| All times are GMT +1. The time now is 08:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com