reference every 19 rows

  • Thread starter Thread starter Scottyb97
  • Start date Start date
S

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
 
=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
 
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
 
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
 
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.
 
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
 
Back
Top