Summing values, x rows deep, depending on a criterion

K

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?
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum of Variable Cells in a fixed Row 3
Automatically populate value of cell dependent on adajecent cell 3
sum and if 4
More on Combined IF Function 2
SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS(C14+1,COLUMN(C2),1, 5
Sum every 6th cell in a row. 3
Stripe out duplicate data 5
strange conditional format behavior 5

Top