reference every 19 rows

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

G

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

S

Scottyb97

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

S

Scottyb97

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

G

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

G

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

S

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.

G

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.