Return a SUM based on month

[lt1990]

I have a work book that shows financial data in columnar format and I'm trying to create a summary sheet that will pull through the sum of the correct amount of months when specified.

I have a drop down box with the months in, and these feed a cell that gives the corresponding number (i.e. Jan = 1, Feb = 2 etc) so for example when I select April, I want the formula to sum the 4 cells across.

I have tried using a SUM & INDEX function as follows but it just pulls the number in the cell through, not the sum of the cells:
=SUM(INDEX(Row of data by month,,cell reference with month number))

Anyone know where I'm going wrong or can suggest a better method?
Thanks in advance for any help!

 

[Jon von der Heyden]

Hi and welcome to MrExcel.

So say you have a series of values in A1:L1

To get a cumulative total you will always need to start at A1, so lets anchor it.

=$A$1

But you want to span across a variable number of columns, in this case 4. So now we have:

=$A$1:INDEX($A$1:$L$1,1,4)

* Note that INDEX returns a range

So for the SUM result:

=SUM($A$1:INDEX($A$1:$L$1,1,4))

 

[lt1990]

Ahh that's fixed it! Thank you so much!!