Cumlative returns by date

[jacklan]

01/11/09	1.12
01/12/09	2.23
01/01/10	1.45
01/02/10	-1.68
01/03/10	2.01
01/04/10	-1.77
01/05/10	1.34
01/06/10	1.76
01/07/10	2.34
01/08/10	-1.23
01/09/10	1.76
01/10/10	2.45
01/11/10	-1.56
01/12/10	1.65
01/01/11	2.34
01/02/11	1.90
I have a table like the above, but larger 2009-2017, and want to calculate yearly cumulative returns (where RHS % returns) is there a simple way to do this, currently playing round with =AVERAGEIFS(Returns!B2:B98,Returns!A2:A98,">=01-Jan-2015",Returns!A2:A98,"<=31-Dec-2015") but I would like the cumulative yearly retuen, not the yearly average. Any help appreciated!!

<tbody>
</tbody>

 

[jtakw]

Hi,

Rather than "hard coding" the date/year in the formula, in which case you'll need a different formula for every year, would this work for you:

Book1
	A	B
1	1/11/2009	1.12
2	1/12/2009	2.23
3	1/1/2010	1.45
4	1/2/2010	-1.68
5	1/3/2010	2.01
6	1/4/2010	-1.77
7	1/5/2010	1.34
8	1/6/2010	1.76
9	1/7/2010	2.34
10	1/8/2010	-1.23
11	1/9/2010	1.76
12	1/10/2010	2.45
13	1/11/2010	-1.56
14	1/12/2010	1.65
15	1/1/2011	2.34
16	1/2/2011	1.9
Returns

Book1
	A	B
1	Year	Cumulative Return
2	2009	3.35
3	2010	8.52
4	2011	4.24
Sheet3

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((YEAR(Returns!A$1:A$16)=A2)*(Returns!B$1:B$16))

B2 formula copied down for all years you need, adjust cell references as needed.
Let me know if you prefer to "hard code" the date/year in the formula(s).