SOLVED

COUNTIF counts wrong

%3CLINGO-SUB%20id%3D%22lingo-sub-1618386%22%20slang%3D%22en-US%22%3ECOUNTIF%20counts%20wrong%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618386%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20found%20a%20problem%20using%20COUNTIF%20function%2C%20when%20trying%20count%20data%20as%20shown%20below%3A%3C%2FP%3E%3CP%3EExcel%20says%20that%20there%20is%207%20matches.%20Looks%20like%20it%20cut%20last%20three%20digits%20for%20counting%20(this%20is%20only%20sample%2C%20but%20it's%20same%20on%20other%20values%20in%20sheet.%20)%20Why%3F%20And%20how%20to%20fix%20this%3F%20This%20happens%20in%20Excel%20from%20Office%202010%20and%202019%20Pro%2C%20so%20this%20is%20not%20version%20relatable.%3C%2FP%3E%3CTABLE%20width%3D%22114%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22114%22%3EVALUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04060330000802020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04060330000802022%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04060330000802023%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04060330000802024%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04060330000802025%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04060330000802028%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04060330000802029%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1618386%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618604%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20counts%20wrong%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618604%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774479%22%20target%3D%22_blank%22%3E%40moe_von%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20uses%20only%2015%20digits.%20Any%20additional%20digit%20will%20be%20changed%20to%200.%3C%2FP%3E%3CP%3EYou%20could%20format%20the%20values%20as%20text%20but%20unfortunately%20COUNTIF()%20does%20a%20type%20conversion%20(text%20to%20number).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20more%20information%20and%20a%20solution%20click%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fnewtonexcelbach.com%2F2017%2F12%2F22%2Fthe-countif-bug-and-how-to-avoid-it%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fnewtonexcelbach.com%2F2017%2F12%2F22%2Fthe-countif-bug-and-how-to-avoid-it%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618830%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20counts%20wrong%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774479%22%20target%3D%22_blank%22%3E%40moe_von%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20I%20hope%20I%20can%20assist%20you%20with%20your%20question.%3C%2FP%3E%3CP%3EChange%20the%20formula%20for%20a%20more%20consistent%20one.%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(N(VALUES%3DYOUR_CRITERIA))%3C%2FP%3E%3CP%3EConsidering%20your%20values%20on%20column%20A%20and%20the%20number%20that%20you%20want%20to%20count%20on%20C1%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(N(A1%3AA10%3DC1))%3C%2FP%3E%3CP%3EOr%20if%20you%20want%20to%20use%20COUNTIF%3C%2FP%3E%3CP%3E%3DCOUNTIF(A1%3AA10%3BCHAR(173)%26amp%3BC1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3EIf%20the%20answer%20is%20what%20you%20are%20looking%20for%2C%20don't%20be%20shy%20on%20hit%20the%20like%20button.%3C%2FP%3E%3CP%3EPlease%20don't%20forget%20to%20mark%20as%20Official%2FBest%20Answer%20to%20help%20the%20other%20members%20find%20it%20too.%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi, I found a problem using COUNTIF function, when trying count data as shown below:

Excel says that there is 7 matches. Looks like it cut last three digits for counting (this is only sample, but it's same on other values in sheet. ) Why? And how to fix this? This happens in Excel from Office 2010 and 2019 Pro, so this is not version relatable.

VALUE
04060330000802020
04060330000802022
04060330000802023
04060330000802024
04060330000802025
04060330000802028
04060330000802029

 

 

 

 

2 Replies
Highlighted
Best Response confirmed by moe_von (Occasional Visitor)
Solution

@moe_von 

Excel uses only 15 digits. Any additional digit will be changed to 0.

You could format the values as text but unfortunately COUNTIF() does a type conversion (text to number).

 

For more information and a solution click here:

https://newtonexcelbach.com/2017/12/22/the-countif-bug-and-how-to-avoid-it/

 

Highlighted

@moe_von 

Hello, I hope I can assist you with your question.

Change the formula for a more consistent one.

=SUMPRODUCT(N(VALUES=YOUR_CRITERIA))

Considering your values on column A and the number that you want to count on C1

=SUMPRODUCT(N(A1:A10=C1))

Or if you want to use COUNTIF

=COUNTIF(A1:A10;CHAR(173)&C1)

 

If the answer is what you are looking for, don't be shy on hit the like button.

Please don't forget to mark as Official/Best Answer to help the other members find it too.