Link to home
Start Free TrialLog in
Avatar of Steve B
Steve BFlag for Australia

asked on

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.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

=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
Avatar of Steve B

ASKER

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
Avatar of Steve B

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)))
Avatar of Steve B

ASKER

Works perfectly to what I need. Thanks.
You could also use MOD to extract a time from a date/time, e.g.

=SUMPRODUCT((MOD(C2:C500,1)>="10:40"+0)*(MOD(C2:C500,1)<="11:25"+0))

regards, barry