sum of multiple rows

[dschneiderch]

Hi,
I have 30 days of data with a data point every 5 minutes (288 points/
day) and would like to have the daily sum of this data. However I'd
like these sums in consecutive rows as opposed to in a column with 287
empty cells between each sum (the sum formula is found in the 3rd
column in the same row as the first data point of each day). I
found that if I do two iterations of this and highlight the sum and
the 287 spaces following each cell with the formula, that I can then
fill the formula all the way down, sort the data and delete blank
values and then resort to get the desired output e.g. April 1- April
30 in a column with the daily sums in the next column. Are there any
cleaner ways to do this that don't require 5 or 6 columns?

I hope this is clear. Thanks for your help
ds

 

[dschneiderch]

Hi,
I have 30 days of data with a data point every 5 minutes (288 points/
day) and would like to have the daily sum of this data.   However I'd
like these sums in consecutive rows as opposed to in a column with 287
empty cells between each sum (the sum formula is found in the 3rd
column in the same row as the first data point of each day).    I
found that if I do two iterations of this and highlight the sum and
the 287 spaces following each cell with the formula, that I can then
fill the formula all the way down, sort the data and delete blank
values and then resort to get the desired output e.g. April 1- April
30 in a column with the daily sums in the next column.  Are there any
cleaner ways to do this that don't require 5 or 6 columns?

I hope this is clear. Thanks for your help
ds

I'm sorry, the sum command is a bad example because sumif works well
enough. How about just picking out every 288th data point and placing
the list of midnight data points in another column?
ds

 

[Gord Dibben]

Enter this in B1 then drag/copy down 30 cells.

=SUM(INDEX(A:A,ROW(A1)*288-287):INDEX(A:A,ROW(A1)*288))


Gord Dibben MS Excel MVP