Summing values, x rows deep, depending on a criterion
 

I've used the following formula
=IF($E$4=1;C11;IF($E$4=2;SUM(C11:C12);IF($E$4=3;SU M(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?
--
Martin Kleiner

Summing values, x rows deep, depending on a criterion
 

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

--
Regards
Roger Govier

"klm" wrote in message
...
I've used the following formula
=IF($E$4=1;C11;IF($E$4=2;SUM(C11:C12);IF($E$4=3;SU M(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?
--
Martin Kleiner

Summing values, x rows deep, depending on a criterion
 

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


"Roger Govier" wrote:

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

--
Regards
Roger Govier

"klm" wrote in message
...
I've used the following formula
=IF($E$4=1;C11;IF($E$4=2;SUM(C11:C12);IF($E$4=3;SU M(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?
--
Martin Kleiner