Hi and thanks in advance for any help provided. I am trying to simplify a
nested if statement formula that sums columns of data based on the number in
another column. Here is an example:

Weeks to Sum Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8
4 100 150 160 170 180 200
200 200
2 50 60 70 70 70 65
60 60
etc...

In the first line, I need to sume the first 4 values for a total of 580, in
the second line I need the sum of the first 2 for a total of 110. It seems
like there must be a simple formula to do this but my search has come up
empty.

Thx

Russell

I'm not clear on what you want. You want a total of the first 4 in the
first row (row 2?), first 2 in row 3? How many rows are you summing?

Barb Reinhardt



"Russell Hill" wrote:

Hi and thanks in advance for any help provided. I am trying to simplify a
nested if statement formula that sums columns of data based on the number in
another column. Here is an example:

Weeks to Sum Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8
4 100 150 160 170 180 200
200 200
2 50 60 70 70 70 65
60 60
etc...

In the first line, I need to sume the first 4 values for a total of 580, in
the second line I need the sum of the first 2 for a total of 110. It seems
like there must be a simple formula to do this but my search has come up
empty.

Thx

Russell

On Tue, 19 Aug 2008 16:46:01 -0700, Russell Hill
wrote:

Hi and thanks in advance for any help provided. I am trying to simplify a
nested if statement formula that sums columns of data based on the number in
another column. Here is an example:

Weeks to Sum Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8
4 100 150 160 170 180 200
200 200
2 50 60 70 70 70 65
60 60
etc...

In the first line, I need to sume the first 4 values for a total of 580, in
the second line I need the sum of the first 2 for a total of 110. It seems
like there must be a simple formula to do this but my search has come up
empty.

Thx

Russell

If the number of Weeks to Sum is in A1, and your data starts in B1, then:

=SUM(OFFSET(A1,0,1,1,A1))

--ron

Here is one solution among many:

=SUM(OFFSET(B2,,,,A2))

Assume that the you enter the items (weeks data) starting in B2 and you
enter the number of columns you want to sum in A2. Then in a blank cell
enter the above formula.

--
Cheers,
Shane Devenshire


"Russell Hill" wrote:

Hi and thanks in advance for any help provided. I am trying to simplify a
nested if statement formula that sums columns of data based on the number in
another column. Here is an example:

Weeks to Sum Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8
4 100 150 160 170 180 200
200 200
2 50 60 70 70 70 65
60 60
etc...

In the first line, I need to sume the first 4 values for a total of 580, in
the second line I need the sum of the first 2 for a total of 110. It seems
like there must be a simple formula to do this but my search has come up
empty.

Thx

Russell

One mo

=IF(A2=0,"",SUM(B2:INDEX(B2:H2,A2)))

Adjust the columns to match.



Russell Hill wrote:

Hi and thanks in advance for any help provided. I am trying to simplify a
nested if statement formula that sums columns of data based on the number in
another column. Here is an example:

Weeks to Sum Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8
4 100 150 160 170 180 200
200 200
2 50 60 70 70 70 65
60 60
etc...

In the first line, I need to sume the first 4 values for a total of 580, in
the second line I need the sum of the first 2 for a total of 110. It seems
like there must be a simple formula to do this but my search has come up
empty.

Thx

Russell

--

Dave Peterson

Thanks all! The OFFSET function is exactly what I was looking for.

"ShaneDevenshire" wrote:

Here is one solution among many:

=SUM(OFFSET(B2,,,,A2))

Assume that the you enter the items (weeks data) starting in B2 and you
enter the number of columns you want to sum in A2. Then in a blank cell
enter the above formula.

--
Cheers,
Shane Devenshire


"Russell Hill" wrote:

Hi and thanks in advance for any help provided. I am trying to simplify a
nested if statement formula that sums columns of data based on the number in
another column. Here is an example:

Weeks to Sum Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8
4 100 150 160 170 180 200
200 200
2 50 60 70 70 70 65
60 60
etc...

In the first line, I need to sume the first 4 values for a total of 580, in
the second line I need the sum of the first 2 for a total of 110. It seems
like there must be a simple formula to do this but my search has come up
empty.

Thx

Russell