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

 

[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