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