Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I want to count if the below fall between the times of 01:00 and 16:00
    I use the following formula but return 0, what is wrong with this


    =COUNTIF($A$2:$A$9,">TIME(1,0,0)")-COUNTIF($A$2:$A$9,">TIME(16,0,0)")

    TIA
    Attached Images Attached Images
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Use

    =COUNTIF($A$2:$A$9,">"&TIME(1,0,0))-COUNTIF($A$2:$A$9,">"&TIME(16,0,0))

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765627' date='17-Mar-2009 00:00']Use

    =COUNTIF($A$2:$A$9,">"&TIME(1,0,0))-COUNTIF($A$2:$A$9,">"&TIME(16,0,0))[/quote]

    Hi Hans

    Thanks for the very fast reply.


    I noticed that your Countif formula use ">"&TIME(1,0,0), how do I know when should I use ">"&....
    rather than ">......"

    How do I attached an image which shows on the post rather than an attachment in this new environment
    of the Lounge. I did as I have done so in the past but this time it became an attachment

    TIA

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The condition argument of COUNTIF and SUMIF must either be a numeric value such as 37 or -5.9, or a string such as ">61" or "<0.23". You can't use formulas within that string: ">TIME(1,0,0)" doesn't work. By using ">"&TIME(1,0,0) you force Excel to evaluate the formula TIME(1,0,0) and to concatenate the result with ">".

    If you want to include a picture within a post, use a .gif, .png or .jpg image, not a .bmp image.

Posting Permissions

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