EXCEL Sum column based on time range in different column?
 

Have spreadsheet that contains column with timestamp and another with count.
Would like to summarize total counts based on hourly interval. Sample
spreadsheet:
00:01:05 10
00:02:10 20
01:05:00 999
02:00:01 9
..
..
..
Would like to total all counts for HOUR(0) "30 another for HOUR(1) "999"
another for HOUR(2) "9" etc.

Thanks

On Thu, 13 Oct 2005 08:51:06 -0700, "RD975"
wrote:

Have spreadsheet that contains column with timestamp and another with count.
Would like to summarize total counts based on hourly interval. Sample
spreadsheet:
00:01:05 10
00:02:10 20
01:05:00 999
02:00:01 9
.
.
.
Would like to total all counts for HOUR(0) "30 another for HOUR(1) "999"
another for HOUR(2) "9" etc.

Thanks

One way is a SUMPRODUCT formula

Assuming your data above is in A1:B4,

In D1:D24 fill in the values 0 -- 24

Then in E1 enter
=SUMPRODUCT((HOUR(A1:A4)=D1)*(B1:B4))
and copy down to E24

Change the A:B range to fit your data.

HTH



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

If your data is in cols A&B, then insert a helper column. In C1 enter
=HOUR(A1) and copy down. You will see:

time count hour
0:01:05 10 0
0:02:10 20 0
1:05:00 999 1
2:00:01 9 2
Then construct a Pivot Table using sumof count in the data field and hour
in the row field.
--
Gary''s Student


"RD975" wrote:

Have spreadsheet that contains column with timestamp and another with count.
Would like to summarize total counts based on hourly interval. Sample
spreadsheet:
00:01:05 10
00:02:10 20
01:05:00 999
02:00:01 9
.
.
.
Would like to total all counts for HOUR(0) "30 another for HOUR(1) "999"
another for HOUR(2) "9" etc.

Thanks