Counting cells with intervals

P

Paulo Araújo

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

Dave R.

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 said:
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
Click to expand...
o'clock.
 
P

Paulo Araújo

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

Ron Rosenfeld

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

=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
 
R

Ron Rosenfeld

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

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
 
Top