MajinGrohl
New Member
- Joined
- Dec 1, 2012
- Messages
- 1
Hello,
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,
Dave.
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,
Dave.
