Summing values, x rows deep, depending on a criterion

[klm]

I've used the following formula
=IF($E$4=1;C11;IF($E$4=2;SUM(C11:C12);IF($E$4=3;SUM(C11:C13);IF($E$4=4;SUM(C11:C14);"na"))))
to sum values in either one, two, three or four rows of data, depending on
the value in cell E4. If I want to go beyond the value 4 I need to add
another nested If statement.

Can I generate a formula that expands the sum range, depending on the value
of cell E4?

 

[Roger Govier]

Hi Martin

Try
=SUM(OFFSET(C11,0,0,MAX(E1,1),1))
The max function is there just to prevent an error if there is no value in
E1

 

[klm]

Offset is the answer to my woes ... Thanks a heap, Roger!