Simple Countif formula not working

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Simple Countif formula not working

  1. #1
    New Member
    Join Date
    Dec 2012
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Simple Countif formula not working

    I am having an issue with a countif formula, which is returning the correct answers for the first few rows, but zero for the rest.
    I have a list of times that I imported from a text file, formatted hh:mm:ss which is in the range A1:A8768, the times range between 10:31:00 and 10:39:00 with duplicates of times appearing, I am trying to count the duplicates. Which is where my formula comes in, I have created a second list of times in column B, this time 10:31:00 to 10:39:00 with no duplicates and in consecutive order, then in column C I have the following formula =COUNTIF($A$1:$A$8768,B1).
    The problem is that the formula works in the first 15 rows and in the 29th row (oddly), but returns "0" for the rest, which I know to be wrong, because I have checked (some of them).
    I have tried entering the countif formula for some of the incorrect cells without the absolute references, that doesn't work, I have also tried converting the dates to numbers, that also doesn't work. I started working in Excel 2003 with the imported txt file of the data, I tried opening the saved 2003 .xls file in 2007, I then tried importing the text file it into Excel 2007, none of which work.
    I entered the formula firstly into the row 1, then double clicked the fill down to copy the formula down to the bottom of the table, for a fraction of a second, the cells containing the incorrect "0" flash with what I'm assuming is the correct answer and then turn to "0"!!!!!!

    Whats happening, what have I missed, can I throw the PC out of the window yet? I could have done the job by hand by now, so much for "I know how to make this tedious analysis much quicker!!!!!"

    Thanks in advance,

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Western NSW
    Post Thanks / Like
    0 Post(s)
    1 Thread(s)

    Default Re: Simple Countif formula not working

    Hi Dave and welcome to the Board
    Without seeing your data shouldn't the formula be
    =IF(COUNTIF($A$1:$A8768,$B1)>1,"Duplicates", "No Match")
    Michael M
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    [CODE]Place Your Code Here[/CODE]

  3. #3
    MrExcel MVP
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Re: Simple Countif formula not working

    Check that your times are serial times using the ISNUMBER function. Also check that the cells contain what you expect when formatted as General.
    Microsoft MVP - Excel

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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