Need rolling summary of data

[Pierre]

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

 

[Don Guillett]

something like this where c1 & d1 contain your desired dates
=sumproduct((month(a2:a22)>=c1-2)*(month(a2:a22)<=d1-2)*b2:b22)

 

[Pierre]

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










- 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

 

[Don Guillett]

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

 

[Pierre]

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









- 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