Count number of occurences within a time range

[zebulebu]

Hi

Hope someone can help me out.

I have a daily spreadsheet of log files that have been generated from a
database and exported to Excel. In preparation for graphing them, I
would like to be able to summarise the number of incidents that were
logged each hour. The format of the spreadsheet is as follows:

A B C D
1 DATE, TIME, MESSAGE, IP ADDRESS
2 07/09/06 00:01:34 <DATA> 10.0.0.0
3 07/09/06 02:04:41 <DATA> 10.0.0.0
4 07/09/06 06:17:20 <DATA> 10.0.0.0

The 'Time' column is formatted to hours/minutes/seconds.

I have been experimenting with COUNTIF and SUMPRODUCT functions, but
haven't managed to come up with anything that gives me what I need. I'd
like to have a formula I can use to create 24 totals (one for each
hour), which I can then use in a graph to show system activity.

Has anyone got any ideas or can point me in the right direction for a
function to do this?

Thanks, in advance!

 

[Biff]

Hi!

How do you want to breakout the time intervals:

12:00:00 AM to 1:00:00 AM

12:00:00 AM to 12:59:59 AM

Try this and just play with the time interval:

To count from 12:00:00 AM to 1:00:00 AM (inclusive):

=COUNTIF(B2:B4,">="&TIME(0,0,0))-COUNTIF(B2:B4,">"&TIME(1,0,0))

Biff

 

[zebulebu]

Cheers Biff

Actually, I've figured out the formula I need.

=SUMPRODUCT(--($B$1:$B$166>=(0/24)),--($B$1:$B$166<=(1/24)))

It works like a charm

Now all I need to do is work out how to suppress zero values from
appearing (I'd like to enter the range as B1:B50000 (each date in the
spreadsheet has a differing number of logs) but when I do this, the
results are obviously skewed for the first hour, as zero values are
counted. At present I've botched my way around this by amending the
formula to read:

=SUMPRODUCT(--($B$1:$B$50000>=(0.00001/24)),--($B$1:$B$50000<=(1/24)))

which works - but isn't exactly elegant!

Thanks for the advice though

Zeb

 

[Biff]

It works like a charm

Now all I need to do is work out how to suppress zero values

Then it must not work like a charm!

Sumproduct will evaluate empty cells as 0 which is why I suggested using
Countif. Countif is also more efficient.

You can add another array to Sumproduct that tests for empty cells:

=SUMPRODUCT(--(ISNUMBER($B$1:$B$166)),--($B$1:$B$166>=0),--($B$1:$B$166<=1/24))

Biff

 

[zebulebu]

Ohhhh Biff - you're a star.

THAT works like a charm!



Thanks a lot

Zeb

 

[Biff]

You're welcome!

Biff

Ohhhh Biff - you're a star.

THAT works like a charm!



Thanks a lot

Zeb