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
__________________________
|