Hi, I am trying to sum the sales values of months (say Jan to Mar) with the
start month being fixed and the end month being a variable. I have therefore
named a cell as my EndMonth and want my sum to change automatically every
time I change my EndMonth variable. ie. if the sales values for Jan to Dec
are in range B2 to M2, I want my sum to change automatically from B2:D2 for
the period Jan to Mar to B2:G2 for the period Jan to Jun.

Is there a function for this? I tried Index for no luck.

A B C D E F
G
EndMonth jan feb mar apr may etc
4 10 25 3 44 5

Put the number of the month in A2 (for april 4)
the formula

=SUM(B2:CHOOSE(A2-1,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2))
is sumarizes for you each row for the range of B2:to the endMonth


Click yes if helped

--
Greatly appreciated
Eva


"Costas Limassol" wrote:

Hi, I am trying to sum the sales values of months (say Jan to Mar) with the
start month being fixed and the end month being a variable. I have therefore
named a cell as my EndMonth and want my sum to change automatically every
time I change my EndMonth variable. ie. if the sales values for Jan to Dec
are in range B2 to M2, I want my sum to change automatically from B2:D2 for
the period Jan to Mar to B2:G2 for the period Jan to Jun.

Is there a function for this? I tried Index for no luck.

Hi Eva,

Thanks for the advise. It worked but I tried to take it a step further so I
can look up the name or account number of the income/expense from a range and
then sum the months. Here is the formula that I've used:

=sum(VLOOKUP($A7;$A$6:$AD$12;18;FALSE):CHOOSE(End_ Period;VLOOKUP($A7;$A$6:$AD$12;18;FALSE);VLOOKUP($ A7;$A$6:$AD$12;19;FALSE);VLOOKUP($A7;$A$6:$AD$12;2 0;FALSE);VLOOKUP($A7;$A$6:$AD$12;21;FALSE);VLOOKUP ($A7;$A$6:$AD$12;22;FALSE);VLOOKUP($A7;$A$6:$AD$12 ;23;FALSE);VLOOKUP($A7;$A$6:$AD$12;24;FALSE);VLOOK UP($A7;$A$6:$AD$12;25;FALSE);VLOOKUP($A7;$A$6:$AD$ 12;26;FALSE);VLOOKUP($A7;$A$6:$AD$12;27;FALSE);VLO OKUP($A7;$A$6:$AD$12;28;FALSE);VLOOKUP($A7;$A$6:$A D$12;29;FALSE)))

I know it's a very complex formula but it seems logical so I can't
understand why it will not accept it saying that the formula contains an
error!!



"Eva" wrote:

A B C D E F
G
EndMonth jan feb mar apr may etc
4 10 25 3 44 5

Put the number of the month in A2 (for april 4)
the formula

=SUM(B2:CHOOSE(A2-1,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2))
is sumarizes for you each row for the range of B2:to the endMonth


Click yes if helped

--
Greatly appreciated
Eva


"Costas Limassol" wrote:

Hi, I am trying to sum the sales values of months (say Jan to Mar) with the
start month being fixed and the end month being a variable. I have therefore
named a cell as my EndMonth and want my sum to change automatically every
time I change my EndMonth variable. ie. if the sales values for Jan to Dec
are in range B2 to M2, I want my sum to change automatically from B2:D2 for
the period Jan to Mar to B2:G2 for the period Jan to Jun.

Is there a function for this? I tried Index for no luck.

Hi, I am trying to sum the sales values of months (say Jan to Mar) with the
start month being fixed and the end month being a variable. ...

If the month number is in A2 (4 for April or 5 for May, etc.) then the
sum is
=SUM(OFFSET(B2,0,0,1,A2))

If instead the month name is in A2, then the sum is
=SUM(OFFSET(B2,0,0,1,MONTH(DATEVALUE(A2&" 1, 2000"))))

Hi,

try this

=sum(B2:index(b1:M2,2,match(B4,B1:M1,0)))

B1:M1 has Jan, Feb etc. In cell B4, enter one of the months mentioned in
range B1:M1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Costas Limassol" wrote in
message ...
Hi, I am trying to sum the sales values of months (say Jan to Mar) with
the
start month being fixed and the end month being a variable. I have
therefore
named a cell as my EndMonth and want my sum to change automatically every
time I change my EndMonth variable. ie. if the sales values for Jan to
Dec
are in range B2 to M2, I want my sum to change automatically from B2:D2
for
the period Jan to Mar to B2:G2 for the period Jan to Jun.

Is there a function for this? I tried Index for no luck.