# Formula to calculate numbers of cells with a specified range

I am trying to create a formula that searches a column of cells that contain a variety of times and counts the numbers of cells within a specified time range.

For instance in column C, there are the following times
8:30
8:45
8:56
9:00
9:05
10:50
Etc

The formula in cell F1 searches this column and counts the number of cells in column C that is between 8:00 and 8:59. In this case the result would be 3.

Hope someone can help.
Saqib Husain

=COUNTIFS(C:C,">=8:00",C:C,"<=8:59")

or

=COUNTIFS(C:C,">8:00",C:C,"<8:59")
Formula in F1:

=SUMPRODUCT(--(C:C>TIME(8,15,0)),--(C:C <=TIME(9,0,0)))

Note that the time function is time(h,m,s)  where
h=hours
m=minutes
s=seconds

so the example above will give a count of times from column C, where the time is greater than 8:15 am and less than or equal to 9:00 am.
COUNTIFS is only available in 2007 or later.

SUMPRODUCT would be compatible with 2003 or you can use the COUNTIF function:

=COUNTIF(C:C,"<9:00")-COUNTIF(C:C,"<8:00")

This says count the number that are less than 9:00 and deduct the count of those that are less than 8:00 thus leaving the count of those between the two times.

Thanks
Rob H
If you are always counting within a one hour period you can use HOUR function like this

=SUMPRODUCT((HOUR(C2:C100)=8)+0)

regards, barry

Hi Guys,
I have tried each formula and they all give me a result of 0, regardless of which formula I use.

I have attached the spreadsheet for you to have a look at to see what is wrong.
PhoneCall-SalesStaff-Usage-2012-.xls
I don't think you tried mine - because your data has dates and times some of the formulas suggested here would need to be adjusted to cope with that (they assume just times) but mine will work anyway, i.e. to count between 08:00 and 08:59:59

=SUMPRODUCT((HOUR(C2:C500)=8)+0)

regards, barry

Hi Barry,
How would I use your fomula if I wanted to count cells that are between specific times. For instance 10:40 to 11:25am?
Ken Butters

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can shorten buttersk's formula to

=SUMPRODUCT(((C:C-INT(C:C))>TIME(8,15,0))*((C:C-INT(C:C)) <=TIME(9,0,0)))

If you are using 2003 you cannot work on entire columns in which case you will have to have defined ranges line

=SUMPRODUCT(((C1:C10000-INT(C1:C10000))>TIME(8,15,0))*((C1:C10000-INT(C1:C10000)) <=TIME(9,0,0)))