Conditional/variable sum??

[Costas Limassol]

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 B22 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.

 

[Eva]

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

 

[Eva]

I have a hard time understanding how your data looks like, so can you show
the exmaple?

 

[Costas Limassol]

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;20;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);VLOOKUP($A7;$A$6:$AD$12;25;FALSE);VLOOKUP($A7;$A$6:$AD$12;26;FALSE);VLOOKUP($A7;$A$6:$AD$12;27;FALSE);VLOOKUP($A7;$A$6:$AD$12;28;FALSE);VLOOKUP($A7;$A$6:$AD$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!!

 

[Costas Limassol]

Hi Eva,

This is an example of my data:

1 A B C D E F G H I J K L M N
2 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
3 € € € € € € € € € € € € €
4
5 Sales 29.871 28.666 28.400 27.936 28.316 27.936
27.586 38.834 28.486 28.936 28.316 27.936
351.220
6 C.O.S.
7 G. P.
8
9 Payroll etc until line 50.

I have changed the references to my formula so it matches the example.
There's also a named area called "Data" with ref A5:N50.

=sum(VLOOKUP($A5;Data;2;FALSE):CHOOSE(End_Period;VLOOKUP($A5;Data;2;FALSE);VLOOKUP($A5;Data;3;FALSE);VLOOKUP($A5;Data;4;FALSE)
VLOOKUP($A5;Data;5;FALSE);VLOOKUP($A5;Data;6;FALSE);VLOOKUP($A5;Data;7;FALSE);VLOOKUP($A5;Data;8;FALSE);VLOOKUP($A5;Data;9;FALSE)
VLOOKUP($A5;Data;10;FALSE);VLOOKUP($A5;Data;11;FALSE);VLOOKUP($A5;Data;12;FALSE);VLOOKUP($A5;Data;13;FALSE)))

I hope this is clearer.

Thanks
Costas

 

[Eva]

Hi
I tried your example, and it looks like vlookup can't work with choose, but
why you need it anyway? you are doing it by line by line, so mayby you can
use my first formula to sum your data by row.
Click yes if helped

 

[zvkmpw]

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"))))

 

[Ashish Mathur]

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