sum and variable rows
 

I give up. Each month I have to sum up a series of rows. I have a worksheet
with all of the data called Raw Data.

Then I have a totals worksheet that has stuff like:

In totals, A1 I have =SUM('Raw Data'!C1959:C2329)
In totals, A2 I have =SUM('Raw Data'!N1959:N2329)

What I would like to do is place the start and stop row numbers in totals!X1
(1959), totals!Y1 (2329) so I don't have to manually enter these ranges each
month. I have not been able to figure out the correct syntax for changing
A1 and A2 to get the row number from a cell. I thought it would be something
like:

=SUM(INDIRECT("'Raw Data'!C"&totals!X1):INDIRECT("'Raw Data'!C&totals!Y1))
but I'm wrong. Question is, am I even in the right ball park. Thanks in
advance.

Hi!

Try this:

=SUM(INDIRECT("'Raw Data'!C"&X1&":C"&Y1))

Really don't know why you want to do this, but that's how it's done.

Biff

"Jim" wrote in message
...
I give up. Each month I have to sum up a series of rows. I have a
worksheet
with all of the data called Raw Data.

Then I have a totals worksheet that has stuff like:

In totals, A1 I have =SUM('Raw Data'!C1959:C2329)
In totals, A2 I have =SUM('Raw Data'!N1959:N2329)

What I would like to do is place the start and stop row numbers in
totals!X1
(1959), totals!Y1 (2329) so I don't have to manually enter these ranges
each
month. I have not been able to figure out the correct syntax for
changing
A1 and A2 to get the row number from a cell. I thought it would be
something
like:

=SUM(INDIRECT("'Raw Data'!C"&totals!X1):INDIRECT("'Raw Data'!C&totals!Y1))
but I'm wrong. Question is, am I even in the right ball park. Thanks in
advance.

Excellent! This is going to save me a lot of time. Thank-you Biff!

"Biff" wrote:

Hi!

Try this:

=SUM(INDIRECT("'Raw Data'!C"&X1&":C"&Y1))

Really don't know why you want to do this, but that's how it's done.

Biff

"Jim" wrote in message
...
I give up. Each month I have to sum up a series of rows. I have a
worksheet
with all of the data called Raw Data.

Then I have a totals worksheet that has stuff like:

In totals, A1 I have =SUM('Raw Data'!C1959:C2329)
In totals, A2 I have =SUM('Raw Data'!N1959:N2329)

What I would like to do is place the start and stop row numbers in
totals!X1
(1959), totals!Y1 (2329) so I don't have to manually enter these ranges
each
month. I have not been able to figure out the correct syntax for
changing
A1 and A2 to get the row number from a cell. I thought it would be
something
like:

=SUM(INDIRECT("'Raw Data'!C"&totals!X1):INDIRECT("'Raw Data'!C&totals!Y1))
but I'm wrong. Question is, am I even in the right ball park. Thanks in
advance.

You're welcome. Thanks for the feedback!

Biff

"Jim" wrote in message
...
Excellent! This is going to save me a lot of time. Thank-you Biff!

"Biff" wrote:

Hi!

Try this:

=SUM(INDIRECT("'Raw Data'!C"&X1&":C"&Y1))

Really don't know why you want to do this, but that's how it's done.

Biff

"Jim" wrote in message
...
I give up. Each month I have to sum up a series of rows. I have a
worksheet
with all of the data called Raw Data.

Then I have a totals worksheet that has stuff like:

In totals, A1 I have =SUM('Raw Data'!C1959:C2329)
In totals, A2 I have =SUM('Raw Data'!N1959:N2329)

What I would like to do is place the start and stop row numbers in
totals!X1
(1959), totals!Y1 (2329) so I don't have to manually enter these ranges
each
month. I have not been able to figure out the correct syntax for
changing
A1 and A2 to get the row number from a cell. I thought it would be
something
like:

=SUM(INDIRECT("'Raw Data'!C"&totals!X1):INDIRECT("'Raw
Data'!C&totals!Y1))
but I'm wrong. Question is, am I even in the right ball park. Thanks
in
advance.

Hi Jim,

Hi Jim,

I guess there is a slightly more robust way (much less volatile than
INDIRECT):

=SUM(INDEX('Raw Data'!C:C,A1):INDEX('Raw Data'!C:C,A2))

Regards,
KL


"Jim" wrote in message
...
I give up. Each month I have to sum up a series of rows. I have a
worksheet
with all of the data called Raw Data.

Then I have a totals worksheet that has stuff like:

In totals, A1 I have =SUM('Raw Data'!C1959:C2329)
In totals, A2 I have =SUM('Raw Data'!N1959:N2329)

What I would like to do is place the start and stop row numbers in
totals!X1
(1959), totals!Y1 (2329) so I don't have to manually enter these ranges
each
month. I have not been able to figure out the correct syntax for
changing
A1 and A2 to get the row number from a cell. I thought it would be
something
like:

=SUM(INDIRECT("'Raw Data'!C"&totals!X1):INDIRECT("'Raw Data'!C&totals!Y1))
but I'm wrong. Question is, am I even in the right ball park. Thanks in
advance.