Excel Help - IF statements

[.Mark]

Chaps, this one has me foxed.
I have a row with month heading Jan thru Dec, the row below has a value.
There is also another field where the month number is entered and next to that a sum is calculated depending on the month.
i.e. Month = 1, total = Jan figure. Month = 2, total = Jan figure plus Feb figure.

I'm using the If statement but can only get up to month 8 as excel will only allow 7 imbeded IF's.

Does anyone know a way around this?
TIA

[Tim2100]

Can you use the SUMIF function, i.e "=sumif(Month range,"month",value range)

would need some more info to help further

[pdV6]

Assumptions:
1. Your month headingss are in the cells A1:L1
2. The month totals are in cells A2:L2
3. The input field for which month is in A4

The following will do the trick:

=SUM(A2:CHOOSE(A4,A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2))

{edited to explain:}

i.e. What you're actually doing is summing a range of values.
The extent of the range depends on your input value (month no.).
The CHOOSE function takes a numeric parameter (here, the content of cell A4) to return a cell from the list supplied.

e.g if A4 contained "1", it would pick the first value supplied (A2), so your result would evaluate to SUM(A2:A2) (i.e. just jan)

If you input 3, the result would evaluate to SUM(A2:C2) (i.e. jan+feb+mar)


>> Edited by pdV6 on Tuesday 9th March 13:31

[TheLemming]

There are a few ways round this one, but they are a real pain to explain...

You could use a VLOOKUP (or HLOOKUP if its arranged horizontally) to deliver the result of a formula for each month.

For example, set up an table with January - December in cells A1 to A12 and the monthly formula for the corresponding month in cells B1 to B12.

If C1 was the cell you typed the current month in and D1 was the cell which delivered your result, the formula in cell D1 would be:

=VLOOKUP(C1,A1:B12,2)

This looks for the value of Cell C1 in the table A1:B12 and delivers the result from the second column.

You could happily put workings such as this on a hidden second sheet within the workbook that nobody will see.

Hope this helps :P

[.Mark]

pdv6 did the trick, now just have to translate that in to my spreadsheets.

Thanks guys!