counting cells within a time range

Posted by Jeff on April 24, 2001 2:01 PM

If I have a list of times:

8:31
8:56
9:04
9:26
9:34
9:54
etc..

How can I count the number of cells consisting of times between 9:00 and 9:30?

Please help!!!

Posted by Dave Hawley on April 24, 2001 2:16 PM

Hi Jeff

If your Times are in Column A, then copy the heading of this columnm to say C1 and D1. The under C1 in C2 put >09:00 then under D1 in D2 put &LT;09:30 now in any cell put:

=DCOUNT(A:A,1,C1:D2)

You could also use an array formula for this, but I believe the DCOUNT is better.

Dave
OzGrid Business Applications

Posted by Jeff on April 24, 2001 2:25 PM

Dave,

You are the greatest! You don't know how thankful I am - I have been trying everything for a long time! It now works because of you!

Just for my curiosity: What is the purpose of including the headers? I know when the headers are taken out, it doesn't work.

Thanks again!

Jeff

Posted by Dave Hawley on April 24, 2001 2:31 PM

You are the greatest! You don't know how thankful I am - I have been trying everything for a long time! It now works because of you! Just for my curiosity: What is the purpose of including the headers? I know when the headers are taken out, it doesn't work. Thanks again! Jeff

Hi Jeff

The Database functions (of which DCOUNT is one) require the headers of the Table they are extracting information from so that they know which columns to use in the criteria.

If you push F1 and type in DCOUNT excel will show you quite a lot of detail on this and all the other database functions.

Dave
OzGrid Business Applications

Posted by Jeff on April 24, 2001 2:33 PM

Thanks again! : Dave, : You are the greatest! You don't know how thankful I am - I have been trying everything for a long time! It now works because of you! : Just for my curiosity: What is the purpose of including the headers? I know when the headers are taken out, it doesn't work. : Thanks again! : Jeff

Posted by Jeff on April 25, 2001 10:04 AM

I have used Dave's solution to my original problem - it works great. But, I have one more question. I have a list of criteria:

(A1)Time In (B1)Time In
(A2)>=09:30 (B2)&LT;10:00
(A3)>=10:00 (B3)&LT;10:30
etc...

Using the DCount function, How do I select row A1 and A3 to only get the number of entries between 10:00 and 10:30? I want to use the function on a list of times (>=9:30 &LT;10:00, >=10:00 &LT;10:30, etc...)

Thanks for all your help!