EXCEL Sum column based on time range in different column?

R

RD975

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
 
R

Richard Buttrey

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
Click to expand...

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

Gary''s Student

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.
 
Top