Forum Discussion
Error in distinct values formula
Alex, I opened your file and see the correct result
Are you on automatic calculation option?
In general, since COUNTIF() converts texts which represent numbers into numbers, perhaps it'll be more reliable to use SUMPRODUCT() here
=IF(SUMPRODUCT(1*(A$2:A2961=A2961))=1,1,0) or =--(SUMPRODUCT(1*(A$2:A2961=A2961))=1)
Alex, I opened your file and see the correct result
Are you on automatic calculation option?
In general, since COUNTIF() converts texts which represent numbers into numbers, perhaps it'll be more reliable to use SUMPRODUCT() here
=IF(SUMPRODUCT(1*(A$2:A2961=A2961))=1,1,0)
or
=--(SUMPRODUCT(1*(A$2:A2961=A2961))=1)
Sergei, thank you for your help.
Your formula works, and now I have 0 as a result of my formula and 1 as a result of yours.
Automatic calculation is turned on.
Do you have any ideas, what might be a reason?
By the way, if I open file Example (2), that you attached, I have 1 in B column as well.
Alex, I'm not sure why we have different behaviour, perhaps different regional settings, but in any case it's better to modify COUNTIF or use SUMPRODUCT.
If second parameter of the COUNTIF() could be interpreted as number (includes dates), COUNTIF converts it to number and only after that checks the range. 1/20, depends on regional settings, could be converted to 20 Jan 2020 which is equivalent of the number 43850 with which Excel compares the range. Perhaps you had same number or something else is converted to same date - I didn't check.
Another way to modify formula is to add soften hyphen before second parameter as
=IF(COUNTIF(A$2:A2,CHAR(173)&A2)=1,1,0)it shall return the same result as SUMPRODUCT.
Bit more about an issue is here http://dailydoseofexcel.com/archives/2006/10/10/countif-bug/
Sergei,
Thanks for your help again.
If I understood correctly the trick with soft hyhpen, it makes Excel to take compared value as text.
The rest is clear to me after some additional studies in the Internet.
Alex, yes. More exactly, it prevents to convert number in text form into number and allows to count them as texts.
Soft hyphen is not the only such character, but that's classic.