Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 __________________________ |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Time Manipulation | Excel Discussion (Misc queries) | |||
How to Join/concatenate a date field with a time field in Excel? | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel | |||
Excel countif based on size of numbers in one column in Access | Excel Worksheet Functions |