match date and shift corresponding data to new date

hhnebula

New Member
Joined
May 15, 2006
Messages
15
I would like to shift the data in column B by a period of time. The whole data series will be shifted based on a specific start date defined in D1. I have entered the formula below in C1 and dragged it down. I want to see the value in B12 and below replicated in C15 and below.

=SUMPRODUCT(--($A$2:$A$170<=$D2),$B$2:$B$170)

Thanks in advance for your assistance!

A B C D
01/01/2011 - 344 01/03/2012
01/02/2011 - 1,665 01/04/2012
01/03/2011 - 2,986 01/05/2012
01/04/2011 - 4,300 01/06/2012
01/05/2011 - 5,574 01/07/2012
01/06/2011 - 6,762 01/08/2012
01/07/2011 - 7,851 01/09/2012
01/08/2011 - 8,849 01/10/2012
01/09/2011 - 9,759 01/11/2012
01/10/2011 - 10,589 01/12/2012
01/11/2011 - 11,351 01/01/2013
01/12/2011 -79 12,068 01/02/2013
01/01/2012 -210 12,741 01/03/2013
01/02/2012 -92 13,375 01/04/2013
01/03/2012 724 13,966 01/05/2013
01/04/2012 1,321 13,966 01/06/2013
01/05/2012 1,321 14,530 01/07/2013
01/06/2012 1,314 15,050 01/08/2013
01/07/2012 1,274 15,532 01/09/2013
01/08/2012 1,188 15,979 01/10/2013
01/09/2012 1,090 16,393 01/11/2013
01/10/2012 998 16,776 01/12/2013
01/11/2012 910 17,129 01/01/2014
01/12/2012 829 17,454 01/02/2014
01/01/2013 762 17,754 01/03/2014
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,224,607
Messages
6,179,871
Members
452,949
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top