Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique text/number cells from a range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting Values that meet another cells criteria | Excel Worksheet Functions | |||
complex fomula: counting cells that are blank | Excel Worksheet Functions | |||
Counting blank cells in Pivot Table | Excel Worksheet Functions |