Count number of occurences within a time range
 

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!

:)

Count number of occurences within a time range
 

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

wrote in message
oups.com...
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!

:)

Count number of occurences within a time range
 

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 wrote:
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

wrote in message
oups.com...
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!

:)

Count number of occurences within a time range
 

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

wrote in message
ups.com...
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 wrote:
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

wrote in message
oups.com...
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!

:)

Count number of occurences within a time range
 

Ohhhh Biff - you're a star.

THAT works like a charm!

:)

Thanks a lot

Zeb


Biff wrote:
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

wrote in message
ups.com...
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 wrote:
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

wrote in message
oups.com...
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!

:)

Count number of occurences within a time range
 

You're welcome!

Biff

wrote in message
ups.com...
Ohhhh Biff - you're a star.

THAT works like a charm!

:)

Thanks a lot

Zeb


Biff wrote:
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

wrote in message
ups.com...
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 wrote:
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

wrote in message
oups.com...
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!

:)

Hi Biff,
I've been trying to figure out solving my problem particularly in this topic but unsuccessful. Could you or anyone who is reading this message help me? I'm working on a report/analysis that displays many crucial data. Out of these data, I'll narrow down to the important part that is (i) Name column, (ii) Date column and (iii) Time stamp (refer to attachment). My records are formatted according to the attached sample file. I'm trying to analyse these data to identify count of instances for each individual, hourly e.g.
(i) 9:00 a.m. to 9:59 a.m. represented by 9AM-10AM
(ii) 10:00 a.m. to 10:59 a.m. represented by 10AM-11AM
(iii) 11:00 a.m. to 11:59 a.m. represented by 11AM-12PM

I've tried using COUNTIFS formula but the results keep displaying "-" symbol. Please help. Thanks.

Regards,
B30K

Hi B30K

You should really start your own thread!

I have put proper times in the cells above your table, see the attached, you can hide these if required. Then using the COUNTIFS in G3: =COUNTIFS($B$2:$B$11,$F3,$D$2:$D$11,"="&F$1,$D$2: $D$11,"<="&G$1) Copy across and down.
Just another note, I have changed the dates in column C to proper dates, they are easier to work with, the same as proper times.

Kevin

Hi Kevin,
Sweet.....you got the code cracked.
I've applied it into my worksheet at it's working as expected.
Thank you so much.

Regards,
B30k

No problem, glad I was able to help.