+ Reply to Thread
Results 1 to 13 of 13

countif does not calculate correctly

  1. #1
    Registered User
    Join Date
    11-13-2019
    Location
    Guelph, Ontario
    MS-Off Ver
    Many
    Posts
    9

    countif does not calculate correctly

    Hello, I am new to this forum and this is my first post.

    I use a formula to calculate number of occurrences a record appears in a column.
    Example:
    Column A consist a list of items from A2 to A65000. Column B is the description, Column C is the formula copied down to the end of C65000.
    =countif($A$2:$A$65000,A2).

    I would expect that the count is correct. And, for most of the items it is, however it is not always. Right now, in this spreadsheet I have one item that shows a resulted count of it 3 times. It appears only once.

    Is this a known bug in Excel?
    Or, there is something wrong with the formula count? I don't think the formula is wrong.

    Your help is greatly appreciated.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: countif does not calculate correctly

    Hi
    seeing the sheet would help as you can see in the yellow banner.
    Thanks

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: countif does not calculate correctly

    do any of the values in A include likes of * or ?

    if so, you might need to do a little substitute - e.g.

    =COUNTIF($A$2:$A$65000,SUBSTITUTE(SUBSTITUTE($A2,"*","~*"),"?","~?"))

  4. #4
    Registered User
    Join Date
    11-13-2019
    Location
    Guelph, Ontario
    MS-Off Ver
    Many
    Posts
    9

    Re: countif does not calculate correctly

    OK.
    Sorry. Need to learn this system.
    How do I attach a file. The attachment icon above does not give me a link to attach a file.

  5. #5
    Registered User
    Join Date
    11-13-2019
    Location
    Guelph, Ontario
    MS-Off Ver
    Many
    Posts
    9

    Re: countif does not calculate correctly

    No. The values do not include any of those special characters.

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: countif does not calculate correctly

    Quote Originally Posted by Mezczyzna View Post
    OK.
    Sorry. Need to learn this system.
    How do I attach a file. The attachment icon above does not give me a link to attach a file.
    Read post #2.....

  7. #7
    Registered User
    Join Date
    11-13-2019
    Location
    Guelph, Ontario
    MS-Off Ver
    Many
    Posts
    9

    Re: countif does not calculate correctly

    OK. I hope it worked.
    Attached Files Attached Files

  8. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: countif does not calculate correctly

    based on the data you should find using

    "*"&$C2

    as criteria will resolve

    as-is 10-3374 is being implicitly coerced to a date by XL (1/Oct/3374) which has a date serial of 538643, and there are 2 instances of that PartNum
    so, without the wildcard prefix (forces XL to treat as text), you're getting a count of 3 for both 10-3374 & 538643 as opposed to 1 & 2, respectively.

    generally speaking the use of * wildcard prefix does introduce some risk of false-positives however, in your dataset you will get the correct result [4243]

  9. #9
    Registered User
    Join Date
    11-13-2019
    Location
    Guelph, Ontario
    MS-Off Ver
    Many
    Posts
    9

    Re: countif does not calculate correctly

    OK Xlent.
    I have to turn my brain around it.
    The data from ERP system should come as text. Is Excel still trying to make it as a 'date number'?

  10. #10
    Registered User
    Join Date
    11-13-2019
    Location
    Guelph, Ontario
    MS-Off Ver
    Many
    Posts
    9

    Re: countif does not calculate correctly

    By the way. Your solution gives correct results.
    Thank you.

  11. #11
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: countif does not calculate correctly

    Quote Originally Posted by Mezczyzna View Post
    Is Excel still trying to make it as a 'date number'?
    Yes, it's one of the nuances of COUNTIF -- it "interprets" the value and if it can coerce it will -- sometimes this behaviour is pretty useful, not here, obviously.

    The * acts a text wildcard in COUNTIF so it, in effect, precludes the coercion
    it does open up possibility of false-positives of course - e.g. if criteria were "car" and you had both "scar" & "car" - the use of "*car" would return a count of 2.
    you can handle this to a point, by testing your "text" criteria yourself, and adjusting accordingly... e.g. IF(ISNUMBER($C2+0),"*"&$C2,$C2) -- this would account for the above type of scenario

  12. #12
    Registered User
    Join Date
    11-13-2019
    Location
    Guelph, Ontario
    MS-Off Ver
    Many
    Posts
    9

    Re: countif does not calculate correctly

    Perfect. Thank you.

  13. #13
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: countif does not calculate correctly

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Countif not totaling correctly
    By morerockin in forum Excel General
    Replies: 7
    Last Post: 02-14-2017, 02:53 PM
  2. [SOLVED] Countif statement not counting correctly for percentages
    By dcoates in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-10-2016, 02:04 PM
  3. [SOLVED] COUNTIF not counting correctly
    By helencj in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-24-2015, 07:42 AM
  4. Countif not counting date correctly
    By ciresuark in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2015, 07:27 PM
  5. Replies: 7
    Last Post: 12-16-2012, 08:24 PM
  6. Excel 2007 : Countif function Not working correctly
    By carl.spackler in forum Excel General
    Replies: 12
    Last Post: 04-09-2012, 06:39 PM
  7. Countif not counting correctly...
    By AaronK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-18-2012, 12:43 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1