Combine SUM and OFFSET

**SaabKen** · 12-23-2011, 05:28 PM

Hi all,

I'm really an Excel novice, so here goes. I have a column of 15-min interval data values (row headings = 00:00, 00:15, 00:30, 00:45, etc) , and I want to create a new column that contains the corresponding hourly sums (ie: 24 rows, starting from 00:00 to 23:00). I'm trying to combine SUM and OFFSET such that in the new hourly summation column, the 00:00 hour's total = aggregation of 00:00->00:45 values (4 rows), and 01:00 hour's total = aggregation of 01:00-01:45 values (4 rows). So for the hourly cell's formula, the reference cell has to move down 4 rows each time to the new starting cell, and its height of values to be added is also 4 rows in height.

Thanks for your help.

**Richard Buttrey** · 12-23-2011, 06:53 PM

Hi,

You'll need to upload your workbook and manually enter the results you expect to see so that we can see your request in context.

**SaabKen** · 12-23-2011, 08:01 PM

Originally Posted by Richard Buttrey

Hi,

You'll need to upload your workbook and manually enter the results you expect to see so that we can see your request in context.

Sure. How do I do that ? [blush]

**Richard Buttrey** · 12-23-2011, 08:23 PM

Hi,

The FAQ explains.

Regards

**SaabKen** · 12-29-2011, 04:49 PM

Hi all,

This is what I'm trying to do:

Excel Question.JPG

So I need a formula starting inside cell D4 that would calculate #1 above, while formula in cell D5 would calculate #2 above, and so on, while I'd be able to copy that same formula all the way down until into the cell that has the calculated sum of Hour 23:00-00:00.