Counting certian rows to sum them
 

Hello, i have a situation that i thought would be pretty simple, and i'm sure
it is for you guru's. Basically if i have a column of 30 numbers i want to
be able to state that i want to sum the first 10 numbers in the column.

A B
10 2
20
20
20
40
50

I want to use the number in B1 as a variable and sum the first 2 rows in
column A yeilding a total of 30. Then if i need to change B1 to 3 i would
get 50 etc etc.

I've gotten close with the offset function, but that works just the
opposite. so any and all help would be great!

thansk
Tim

Counting certian rows to sum them
 

Just set the OFFSET's height param to point to B1, viz:
In say, C1: =SUM(OFFSET(A1,,,B1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"T5s" wrote:
Hello, i have a situation that i thought would be pretty simple, and i'm sure
it is for you guru's. Basically if i have a column of 30 numbers i want to
be able to state that i want to sum the first 10 numbers in the column.

A B
10 2
20
20
20
40
50

I want to use the number in B1 as a variable and sum the first 2 rows in
column A yeilding a total of 30. Then if i need to change B1 to 3 i would
get 50 etc etc.

I've gotten close with the offset function, but that works just the
opposite. so any and all help would be great!

thansk
Tim

Counting certian rows to sum them
 

Try

=IF(B1=0,0,SUM(A1:INDEX(A:A,B1)))


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"T5s" wrote in message
...
Hello, i have a situation that i thought would be pretty simple, and i'm
sure
it is for you guru's. Basically if i have a column of 30 numbers i want
to
be able to state that i want to sum the first 10 numbers in the column.

A B
10 2
20
20
20
40
50

I want to use the number in B1 as a variable and sum the first 2 rows in
column A yeilding a total of 30. Then if i need to change B1 to 3 i would
get 50 etc etc.

I've gotten close with the offset function, but that works just the
opposite. so any and all help would be great!

thansk
Tim

Counting certian rows to sum them
 

Awsome, i was so close, had the b1 in the wrong sequence. works great now!!!

Thanks MAX

"Max" wrote:

Just set the OFFSET's height param to point to B1, viz:
In say, C1: =SUM(OFFSET(A1,,,B1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"T5s" wrote:
Hello, i have a situation that i thought would be pretty simple, and i'm sure
it is for you guru's. Basically if i have a column of 30 numbers i want to
be able to state that i want to sum the first 10 numbers in the column.

A B
10 2
20
20
20
40
50

I want to use the number in B1 as a variable and sum the first 2 rows in
column A yeilding a total of 30. Then if i need to change B1 to 3 i would
get 50 etc etc.

I've gotten close with the offset function, but that works just the
opposite. so any and all help would be great!

thansk
Tim

Counting certian rows to sum them
 

You're welcome <g !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
T5s wrote:
Awesome, i was so close, had the b1 in the wrong sequence. works great now!!!

Thanks MAX

Counting certian rows to sum them
 

Hi,
Type this formula in a desired cell, say C2, to get the result you want:

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

This assumes that your data to be sumed starts from cell A2.

If you want to copy the formula to many cells, convert A2 to $A$2 and B2 to
$B$2.

Viral Sheth

"T5s" wrote:

Hello, i have a situation that i thought would be pretty simple, and i'm sure
it is for you guru's. Basically if i have a column of 30 numbers i want to
be able to state that i want to sum the first 10 numbers in the column.

A B
10 2
20
20
20
40
50

I want to use the number in B1 as a variable and sum the first 2 rows in
column A yeilding a total of 30. Then if i need to change B1 to 3 i would
get 50 etc etc.

I've gotten close with the offset function, but that works just the
opposite. so any and all help would be great!

thansk
Tim