Steve B

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.

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.

Formula in F1:

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.

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

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

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

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

=SUMPRODUCT((HOUR(C2:C100)

regards, barry

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

=SUMPRODUCT((HOUR(C2:C500)

regards, barry

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?

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

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)))

=SUMPRODUCT(((C:C-INT(C:C)

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-IN

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

=SUMPRODUCT((MOD(C2:C500,1

regards, barry

or

=COUNTIFS(C:C,">8:00",C:C,