| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete rows that don't meet specific criterion | New Users to Excel | |||
| Sum Count of Numeric Criterion for LAST 5 Rows | Excel Worksheet Functions | |||
| counting rows depending on 2 selective values | Excel Discussion (Misc queries) | |||
| summing up contents on ceratin cells depending on selection criter | Excel Worksheet Functions | |||
| How do I get the number of rows that meet an autofilter criterion. | Excel Worksheet Functions | |||
![klm[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
