reference every 19 rows
 

trying to figure out how to write a formula the references every 19 rows. On
my worksheet 4 I want A1 =SUM(Sheet1!A1:A19)
and I want A2 =SUM(Sheet1!A20:A38)
I want to follow this pattern for all of worksheet 4....is there a way to
not have to manually write all these equations???
thank
-Scott

reference every 19 rows
 

=SUM(INDEX(Sheet1!A:A,ROW(A1)*19-18):INDEX(Sheet1!A:A,ROW(A1)*19))

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Mon, 2 Jun 2008 15:34:00 -0700, Scottyb97
wrote:

trying to figure out how to write a formula the references every 19 rows. On
my worksheet 4 I want A1 =SUM(Sheet1!A1:A19)
and I want A2 =SUM(Sheet1!A20:A38)
I want to follow this pattern for all of worksheet 4....is there a way to
not have to manually write all these equations???
thank
-Scott

reference every 19 rows
 

Thanks for the input but this equation doesn't seem to be working.


"Gord Dibben" wrote:

=SUM(INDEX(Sheet1!A:A,ROW(A1)*19-18):INDEX(Sheet1!A:A,ROW(A1)*19))

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Mon, 2 Jun 2008 15:34:00 -0700, Scottyb97
wrote:

trying to figure out how to write a formula the references every 19 rows. On
my worksheet 4 I want A1 =SUM(Sheet1!A1:A19)
and I want A2 =SUM(Sheet1!A20:A38)
I want to follow this pattern for all of worksheet 4....is there a way to
not have to manually write all these equations???
thank
-Scott

reference every 19 rows
 

Gord Dibben .... I was wondering if there was anyway we could chat?


"Scottyb97" wrote:

trying to figure out how to write a formula the references every 19 rows. On
my worksheet 4 I want A1 =SUM(Sheet1!A1:A19)
and I want A2 =SUM(Sheet1!A20:A38)
I want to follow this pattern for all of worksheet 4....is there a way to
not have to manually write all these equations???
thank
-Scott

reference every 19 rows
 

Did you enter it in A1 of Sheet4?

Did you copy it down?

What do you get that leads you to say "is not working"?


Gord

On Mon, 2 Jun 2008 16:32:21 -0700, Scottyb97
wrote:

Thanks for the input but this equation doesn't seem to be working.


"Gord Dibben" wrote:

=SUM(INDEX(Sheet1!A:A,ROW(A1)*19-18):INDEX(Sheet1!A:A,ROW(A1)*19))

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Mon, 2 Jun 2008 15:34:00 -0700, Scottyb97
wrote:

trying to figure out how to write a formula the references every 19 rows. On
my worksheet 4 I want A1 =SUM(Sheet1!A1:A19)
and I want A2 =SUM(Sheet1!A20:A38)
I want to follow this pattern for all of worksheet 4....is there a way to
not have to manually write all these equations???
thank
-Scott

reference every 19 rows
 

The way newsgroups work is that you ask questions (and follow-up questions
if necessary), and anyone who believes that they can usefully contribute to
the discussion will answer. That way, the answer will be of benefit not
only to the original questioner but also to other readers of the newsgroup,
either now, or later through the group archives.

And if you are trying to reply to a message from Gord, make sure that you
reply to his message, rather than to your own original, and quote enough of
the previous content to put your reply into context.
--
David Biddulph

"Scottyb97" wrote in message
...
Gord Dibben .... I was wondering if there was anyway we could chat?


"Scottyb97" wrote:

trying to figure out how to write a formula the references every 19 rows.
On
my worksheet 4 I want A1 =SUM(Sheet1!A1:A19)
and I want A2 =SUM(Sheet1!A20:A38)
I want to follow this pattern for all of worksheet 4....is there a way to
not have to manually write all these equations???
thank
-Scott

reference every 19 rows
 

Stick with the thread please.

There are many here who can assist.

Your original post asked for the SUM of each group of 19 cells in column A of
Sheet1.

You wanted this to appear on Sheet4 starting at A1

The formula I posted should do that unless I misunderstand your needs.


Gord

On Mon, 2 Jun 2008 16:42:00 -0700, Scottyb97
wrote:

Gord Dibben .... I was wondering if there was anyway we could chat?


"Scottyb97" wrote:

trying to figure out how to write a formula the references every 19 rows. On
my worksheet 4 I want A1 =SUM(Sheet1!A1:A19)
and I want A2 =SUM(Sheet1!A20:A38)
I want to follow this pattern for all of worksheet 4....is there a way to
not have to manually write all these equations???
thank
-Scott

reference every 19 rows
 

Gord,
I have a question similar to the previous. I am working on hourly
precipitation data where column A is the date and hour and column C is the
associated hourly precip. I would like to condense the 60,000 rows to only
have daily totals. Therefore, in column D, every 24th row I would like the
sum of previous 24 hour precip accumulations. Does that make sense?

Thank you.

"Gord Dibben" wrote:

Stick with the thread please.

There are many here who can assist.

Your original post asked for the SUM of each group of 19 cells in column A of
Sheet1.

You wanted this to appear on Sheet4 starting at A1

The formula I posted should do that unless I misunderstand your needs.


Gord

On Mon, 2 Jun 2008 16:42:00 -0700, Scottyb97
wrote:

Gord Dibben .... I was wondering if there was anyway we could chat?


"Scottyb97" wrote:

trying to figure out how to write a formula the references every 19 rows. On
my worksheet 4 I want A1 =SUM(Sheet1!A1:A19)
and I want A2 =SUM(Sheet1!A20:A38)
I want to follow this pattern for all of worksheet 4....is there a way to
not have to manually write all these equations???
thank
-Scott

reference every 19 rows
 

You could use DataSubtotals and subtotal by day.

This would give you subtotals of column C numbers for each day.

Or use this formula in D1 and copy down.

=SUM(INDEX(C:C,ROW(C1)*24-23):INDEX(C:C,ROW(C1)*24))


Gord


On Tue, 1 Jul 2008 13:56:00 -0700, susan
wrote:

Gord,
I have a question similar to the previous. I am working on hourly
precipitation data where column A is the date and hour and column C is the
associated hourly precip. I would like to condense the 60,000 rows to only
have daily totals. Therefore, in column D, every 24th row I would like the
sum of previous 24 hour precip accumulations. Does that make sense?

Thank you.

"Gord Dibben" wrote:

Stick with the thread please.

There are many here who can assist.

Your original post asked for the SUM of each group of 19 cells in column A of
Sheet1.

You wanted this to appear on Sheet4 starting at A1

The formula I posted should do that unless I misunderstand your needs.


Gord

On Mon, 2 Jun 2008 16:42:00 -0700, Scottyb97
wrote:

Gord Dibben .... I was wondering if there was anyway we could chat?


"Scottyb97" wrote:

trying to figure out how to write a formula the references every 19 rows. On
my worksheet 4 I want A1 =SUM(Sheet1!A1:A19)
and I want A2 =SUM(Sheet1!A20:A38)
I want to follow this pattern for all of worksheet 4....is there a way to
not have to manually write all these equations???
thank
-Scott