Jun 22 2020 12:27 AM - edited Jun 22 2020 12:31 AM
Attached file contains list of numbers in column A and a formula to check, if a number is distinct, in column B.
In some cases, the formula doesn't work as expected. For instance, line 2961 contains number "1/20", which isn't encountered anywhere else in the file. But the formula doesn't count it as distinct. I added COUNTIF to column C to check, how many times there's this value in the column, according to Excel, and it returns 2.
Jun 22 2020 04:38 AM
SolutionAlex, 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)
Jun 22 2020 06:32 AM - edited Jun 22 2020 07:09 AM
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.
Jun 22 2020 07:29 AM
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/
Jun 23 2020 04:50 AM
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.
Jun 23 2020 05:01 AM
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.
Jun 22 2020 04:38 AM
SolutionAlex, 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)