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.

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

With two or more criteria, use SUMPRODUCT, which functions like COUNTIF in this case.

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

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)

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.

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

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.

Thanks for getting back with that.