SOLVED

Error in distinct values formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1480175%22%20slang%3D%22en-US%22%3EError%20in%20distinct%20values%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1480175%22%20slang%3D%22en-US%22%3E%3CP%3EAttached%20file%20contains%20list%20of%20numbers%20in%20column%20A%20and%20a%20formula%20to%20check%2C%20if%20a%20number%20is%20distinct%2C%20in%20column%20B.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20some%20cases%2C%20the%20formula%20doesn't%20work%20as%20expected.%20For%20instance%2C%20line%202961%20contains%20number%20%221%2F20%22%2C%20which%20isn't%20encountered%20anywhere%20else%20in%20the%20file.%20But%20the%20formula%20doesn't%20count%20it%20as%20distinct.%20I%20added%20COUNTIF%20to%20column%20C%20to%20check%2C%20how%20many%20times%20there's%20this%20value%20in%20the%20column%2C%20according%20to%20Excel%2C%20and%20it%20returns%202.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1480175%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1480609%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20in%20distinct%20values%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1480609%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706375%22%20target%3D%22_blank%22%3E%40Alex_Maslovskiy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlex%2C%20I%20opened%20your%20file%20and%20see%20the%20correct%20result%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20422px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F200024i3999771485E2EEE7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAre%20you%20on%20automatic%20calculation%20option%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%2C%20since%20COUNTIF()%20converts%20texts%20which%20represent%20numbers%20into%20numbers%2C%20perhaps%20it'll%20be%20more%20reliable%20to%20use%20SUMPRODUCT()%20here%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(SUMPRODUCT(1*(A%242%3AA2961%3DA2961))%3D1%2C1%2C0)%0Aor%0A%3D--(SUMPRODUCT(1*(A%242%3AA2961%3DA2961))%3D1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1480874%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20in%20distinct%20values%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1480874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESergei%2C%20thank%20you%20for%20your%20help.%3C%2FP%3E%3CP%3EYour%20formula%20works%2C%20and%20now%20I%20have%200%20as%20a%20result%20of%20my%20formula%20and%201%20as%20a%20result%20of%20yours.%3C%2FP%3E%3CP%3EAutomatic%20calculation%20is%20turned%20on.%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20ideas%2C%20what%20might%20be%20a%20reason%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Alex_Maslovskiy_0-1592832734582.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F200038i85BF36A387E5C012%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Alex_Maslovskiy_0-1592832734582.png%22%20alt%3D%22Alex_Maslovskiy_0-1592832734582.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20if%20I%20open%20file%20Example%20(2)%2C%20that%20you%20attached%2C%20I%20have%201%20in%20B%20column%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1481054%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20in%20distinct%20values%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1481054%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706375%22%20target%3D%22_blank%22%3E%40Alex_Maslovskiy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlex%2C%20I'm%20not%20sure%20why%20we%20have%20different%20behaviour%2C%20perhaps%20different%20regional%20settings%2C%20but%20in%20any%20case%20it's%20better%20to%20modify%20COUNTIF%20or%20use%20SUMPRODUCT.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20second%20parameter%20of%20the%20COUNTIF()%20could%20be%20interpreted%20as%20number%20(includes%20dates)%2C%20COUNTIF%20converts%20it%20to%20number%20and%20only%20after%20that%20checks%20the%20range.%201%2F20%2C%20depends%20on%20regional%20settings%2C%20could%20be%20converted%20to%2020%20Jan%202020%20which%20is%20equivalent%20of%20the%20number%2043850%20with%20which%20Excel%20compares%20the%20range.%20Perhaps%20you%20had%20same%20number%20or%20something%20else%20is%20converted%20to%20same%20date%20-%20I%20didn't%20check.%3C%2FP%3E%0A%3CP%3EAnother%20way%20to%20modify%20formula%20is%20to%20add%20soften%20hyphen%20before%20second%20parameter%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(COUNTIF(A%242%3AA2%2CCHAR(173)%26amp%3BA2)%3D1%2C1%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eit%20shall%20return%20the%20same%20result%20as%20SUMPRODUCT.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBit%20more%20about%20an%20issue%20is%20here%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fdailydoseofexcel.com%2Farchives%2F2006%2F10%2F10%2Fcountif-bug%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fdailydoseofexcel.com%2Farchives%2F2006%2F10%2F10%2Fcountif-bug%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483390%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20in%20distinct%20values%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483390%22%20slang%3D%22en-US%22%3E%3CP%3ESergei%2C%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%20again.%3C%2FP%3E%3CP%3EIf%20I%20understood%20correctly%20the%20trick%20with%20soft%20hyhpen%2C%20it%20makes%20Excel%20to%20take%20compared%20value%20as%20text.%3C%2FP%3E%3CP%3EThe%20rest%20is%20clear%20to%20me%20after%20some%20additional%20studies%20in%20the%20Internet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483403%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20in%20distinct%20values%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706375%22%20target%3D%22_blank%22%3E%40Alex_Maslovskiy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlex%2C%20yes.%20More%20exactly%2C%20it%20prevents%20to%20convert%20number%20in%20text%20form%20into%20number%20and%20allows%20to%20count%20them%20as%20texts.%3C%2FP%3E%0A%3CP%3ESoft%20hyphen%20is%20not%20the%20only%20such%20character%2C%20but%20that's%20classic.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

5 Replies
Best Response confirmed by Alex_Maslovskiy (New Contributor)
Solution

@Alex_Maslovskiy 

Alex, I opened your file and see the correct result

image.png

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 Baklan 

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? 

Alex_Maslovskiy_0-1592832734582.png

By the way, if I open file Example (2), that you attached, I have 1 in B column as well.

@Alex_Maslovskiy 

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_Maslovskiy 

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.