May 22 2019 06:21 AM
Hello all,
I am working with a table consisting of three columns. One column has months, the other has revenues and in the 3rd column I need the sum of the last 12 months of revenue. This means that the SUM column will have its first entry on the 12th row, when 12 months of revenue is available. So for example for rows Jan- Dec 2010 There will be one entry in the SUM column, and on the next row the column will now display the summed revenues for Feb 2010- Jan 2011. I need this to keep updating in time as entries are made.
I thought this would be as simple as dragging down the first cell with this formula, but excel recognizes the sum formula itself as an error (for not including all nearby cells). I'm sure there is an easy solution but I am struggling to find it. Any ideas? Thanks!
May 22 2019 06:42 AM
Solution@dzhogov , for the model like this
you may use
=SUM(INDEX(B:B,ROW()-MIN(12,ROW()-ROW($A$1)+1)+1):INDEX(B:B,ROW()))
in C2 and drag it down.
May 22 2019 06:54 AM
May 22 2019 06:42 AM
Solution@dzhogov , for the model like this
you may use
=SUM(INDEX(B:B,ROW()-MIN(12,ROW()-ROW($A$1)+1)+1):INDEX(B:B,ROW()))
in C2 and drag it down.