COUNTIF inconsistency

**webfork** · 03-22-2011, 03:57 PM

I came across a great tip for finding duplicates in long data lists (mrexcel.com/tip138.shtml), but its only working some of the time.

The formula:

Please Login or Register  to view this content.

I have long number strings in 5,000 row spreadsheets
I'm trying to find duplicates in numbers that look like this: "014824-00242"
The COUNTIF command will sometimes display accurately, sometimes list everything as "TRUE" regardless of the content. Sometimes it does.

Screencap:
\1

Attempted workarounds:

I have pasted into a fresh CSV spreadsheet to try and get any odd formatting out, then saving back into an XLSX file.
Also, restarting Excel seem to do the trick, before but it doesn't anymore.

Does anyone have any idea what could be causing this? Its driving me crazy that it was one moment solving problems and the next ignoring my data.

Thanks!

**arthurbr** · 03-22-2011, 04:04 PM

There is no inconsistency at all here

First the countif is evaluated and of course returns 0
Then your formula checks if 0=0 which is TRUE

Try the formula auditing to see the formula at work

EDIT : remove the "=0" part, and all will be well

**ConneXionLost** · 03-22-2011, 04:07 PM

The formula in your picture appears to be checking the value in cell C2, but is in row 3 (at least). You may need to check your cell/row alignment in your formula.

Cheers,

**martindwilson** · 03-22-2011, 04:10 PM

post a workbook not an image, but your image has the wrong formula in, the mr excel tip assumes you have header rows with data starting in c2 ,so g2 would have the formula =COUNTIF(C$1:C1,C2)=0 when dragged down this would increment so g3 would read =COUNTIF(C$1:C2,C3)=0
and g4 =COUNTIF(C$1:C3,C4)=0
and so on

**webfork** · 03-22-2011, 04:40 PM

Thanks everyone who replied so quick and with so many suggestions. That really helped me out of a jam.

The field didn't update after I tried some of the fixes suggested, but it is working now and I'm not sure why. I saved a temporary spreadsheet as an XLSX format document (was using XLS by default for compatibility reasons) and that seemed to trigger the refresh in the main doc (which was already saved as XLSX).

I have switched over to use XLSX by default in settings just to be safe.

Originally Posted by martindwilson

post a workbook not an image, but your image has the wrong formula in

Yeah sorry, that was a pretty little-league mistake.

UPDATE: Yes, saving triggers a refresh. Resolved. Thanks again.

**arthurbr** · 03-23-2011, 03:39 AM

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**webfork** · 03-24-2011, 05:25 PM

> Your post does not comply with Rule 9 of our Forum [/I][URL="http://www.excelforum.com/forum-rules/642590-forum-rules.html"]

Yes, I was breaking that rule. I had difficulty with this because I clicked the 'open in a new tab' button in Firefox. I thought one of my Firefox plugins was interfering and I didn't have an easy way to disable those at time of posting.

> If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.

I thought I did -- saving caused a refresh?