Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    31

    Unanswered: countif problem...

    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. #2
    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. #3
    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)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    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. #5
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    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. #7
    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. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Thanks for getting back with that.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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