Counting cells with intervals
 

I need help.
I've a sheet with one column with hour of some events. How can I count the
number events that happens in a certain interval of time. For exemple. "I
want to know the number of events that happens between 1:01 and 2:00 o'clock.

If they are stored as actual times, try something like this with your times
in B5:B8.

=SUMPRODUCT((24*(B5:B8)=1)*(24*(B5:B8)<2))


"Paulo Araújo" <Paulo wrote in message
...
I need help.
I've a sheet with one column with hour of some events. How can I count the
number events that happens in a certain interval of time. For exemple. "I
want to know the number of events that happens between 1:01 and 2:00
o'clock.

Thanks Dave,
I Tried to do what you said but the result was not what I need.
I'm Trying to use the "COUNTIF" command. But I don't know how insert more
tha one condition.
I transformed the hour in number and I used "=COUNTIF(F4:F6000;"<=0,083333")
(0,083333 is equal 2 o'clock) the result is ok. the problem is when I try
to use "COUNTIF(F4:F6000) to find the number of events betwen 0,083333
(02:01) and 0,166667 (04:00) for exemple.

Paulo


"Dave R." escreveu:

If they are stored as actual times, try something like this with your times
in B5:B8.

=SUMPRODUCT((24*(B5:B8)=1)*(24*(B5:B8)<2))


"Paulo Araújo" <Paulo wrote in message
...
I need help.
I've a sheet with one column with hour of some events. How can I count the
number events that happens in a certain interval of time. For exemple. "I
want to know the number of events that happens between 1:01 and 2:00
o'clock.

On Thu, 23 Jun 2005 11:06:03 -0700, "Paulo Araújo" <Paulo
wrote:

I need help.
I've a sheet with one column with hour of some events. How can I count the
number events that happens in a certain interval of time. For exemple. "I
want to know the number of events that happens between 1:01 and 2:00 o'clock.

=COUNTIF(rng,"="&StartTime) - COUNTIF(rng,""&EndTime)

Format the result as General (Excel will try to format it as time).

You may need to play with the equality statements and your precise EndTime and
StartTime to get exactly the result you are looking for.

As written, with your times, the formula will give the number of instances

Including 1:01
All instances up and including 2:00

It will not give any instances prior to 1:01.


--ron

On Thu, 23 Jun 2005 15:32:37 -0400, Ron Rosenfeld
wrote:

On Thu, 23 Jun 2005 11:06:03 -0700, "Paulo Araújo" <Paulo
wrote:

I need help.
I've a sheet with one column with hour of some events. How can I count the
number events that happens in a certain interval of time. For exemple. "I
want to know the number of events that happens between 1:01 and 2:00 o'clock.

=COUNTIF(rng,"="&StartTime) - COUNTIF(rng,""&EndTime)

Format the result as General (Excel will try to format it as time).

You may need to play with the equality statements and your precise EndTime and
StartTime to get exactly the result you are looking for.

As written, with your times, the formula will give the number of instances

Including 1:01
All instances up and including 2:00

It will not give any instances prior to 1:01.


--ron

Just to elaborate, the StartTime and EndTime should be Excel times.

You could have them in cell references.

Or you could enter a formula such as TIME(1,1,0) for 1:01 AM; etc.




--ron