reference every 19 rows

[Scottyb97]

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

 

[Gord Dibben]

=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

 

[Scottyb97]

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

 

[Scottyb97]

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

 

[Gord Dibben]

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

 

[David Biddulph]

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.

 

[Gord Dibben]

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

 

[susan]

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]

You could use Data>Subtotals 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