1. Registered User
Join Date
Jan 2004
Posts
31

I have a spreadsheet in which I am trying to count the number of tickets opened in a given time range. The column data goes from C2:C530 and it includes time data in the form of HH:MMS AM/PM. The first thing I did was changed this to 24 HR format HH:MM. I tried the following countif statement and keep getting zero returned for the result.. can someone please show me where I am going wrong with this?

COUNTIF(C2:C530,">=00:00 AND <=00:59) I tried removing the
quotes but excel didn't
like that too well.
Any help would be appreciated.

2. Registered User
Join Date
Jan 2004
Posts
31
I've tried the countif using the following method too and it still gives "0" for the count.

=COUNTIF(C2:C530,AND(">=02:00")*("<=02:59 "))

tried this because I've seen where some functions need the #'s around a date function..excel didn't care for it too much.
=COUNTIF(C2:C100,AND(#>=02:00#)*(#<=02:59#))

still playing with it though...any ideas?

Thanks

3. Registered User
Join Date
Oct 2003
Posts
1,091
With two or more criteria, use SUMPRODUCT, which functions like COUNTIF in this case.

=SUMPRODUCT((A1>=02:00")*(A1<=02:59 "),C2:C530)

4. Registered User
Join Date
Jan 2004
Posts
31
what does the A1 do in the formula below or better yet, where does it come from? Does it need quotes around each criteria? All I'm trying to do is count the number of times this column is between a given hour range.

=SUMPRODUCT((A1>=02:00")*(A1<=02:59 "),C2:C530)

should this be

=SUMPRODUCT(("A1>=02:00")*("A1<=02:59"),C2:C530)

5. Registered User
Join Date
Oct 2003
Posts
1,091
Sorry this has not been my week. I was in a hurry when I answered and didn't read your OP carefully enough. I was giving a generic response, not to yours.

I apologize. I will work on it as I have a chance. Or there are others that probably can jump in.

6. Registered User
Join Date
Jan 2004
Posts
31
Hi all,

I still haven't figured out where I'm going wrong with this function. I've tried entering it several different ways but every time it enters a "0" into the field where I want the count of the number of times data was entered whithing a certain hour range. I'm trying to get this info to utilize in a graph that shows time on one axis and a count on the other showing when the most instances occur. Any help with this function or another that would essentially do the same thing would be appreciated.

Frank

7. Registered User
Join Date
Jan 2004
Posts
31
Ok I figured it out...this is what I had to do

COUNTIF(C2:C520,">=00:00")-COUNTIF(C2:C520,">01:00")

this returns the number of tickets between 00:00 and 01:00
I did this for a 24hr period and was able to graph the frequency of tickets for certain hours. If this helps anyone, great.

8. Registered User
Join Date
Oct 2003
Posts
1,091
Thanks for getting back with that.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•