multiple If formulas in one cell

[Bilingual]

Hi everybody, i have an issue with an IF formula, how can i make it return several values, acording to what condition is fullfilled?

I have a datasheet where o have made a datatvalidation roll down menu, which can choose between the 12 months.
I need Excel to return the sum of following:

If January is chosen in the datavalidation roll down menu, it should return the sum of January, if February is chosen it should return the total sum of Jan and Feb, etc...

My formula is working fine with January, but will not work with the rest of the months.

=IF(Q2=R2;SUM(B2);0); IF(Q2=R3;SUM(B2;C2);0);IF(Q2=R4;SUM(B2:D2);0);IF(Q2=R5;SUM(B2:E2);0); IF(Q2=R6;SUM(B2;F2);0);IF(Q2=R7;SUM(B2:G2);0);IF(Q2=R8;SUM(B2:H2);0); IF(Q2=R9;SUM(B2;I2);0);IF(Q2=R10;SUM(B2:J2);0);IF(Q2=R11;SUM(B2:K2);0); IF(Q2=R12;SUM(B2;L2); IF(Q2=R13;SUM(B2:ML2)

 

[Andrew Poulsom]

Is your data validation based on a list on a worksheet? If so what range does it refer to?

 

[Bilingual]

Hi Andrew, the data validation range is simply based on a list in the worksheet of the twelve months, the idea is that i can choose the month, and then excel automatically can calculate a YTD with the months involved.

 

[Andrew Poulsom]

Yes, but is that list on a worksheet? If so what is the name of the worksheet and what range does the list occupy?

 

[Weaver]

I think what you need is an offset function

=sum(offset(B2,0,0,1,month(Q2)))

Might be a bit tricky with the 13 month calendar, but you could maybe use a formula with the number of days of the year so far divided by 28

 

[Bilingual]

The list is in the same worksheet, Q2 is where the data validation range is and where i can choose the month. R2-R13 is the list, where the twelve months are.

 

[Andrew Poulsom]

Try:

=IF(LEN(Q2);SUM(B2:INDEX(B2:M2;MATCH(Q2;R2:R13;FALSE)));"")