+ Reply to Thread
Results 1 to 6 of 6

COUNTIF brings back too many numbers

  1. #1
    Registered User
    Join Date
    09-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    35

    COUNTIF brings back too many numbers

    I am trying to count the frequency of values using countif. In sheet1 I have a long list (around 35,000) of values, some are duplicated thousands of times, others hundreds, others not at all. I want to find out how many times each value appears. I copied and pastes the whole column into sheet2, removed duplicates then did a countif =countif(sheet1!$A$1:$A$3500,sheet2!A1).

    Yet it brings back some odd results. One value which only appears twice in sheet1 is counted too many times and in sheet 2 says there are over 30,000. When I add up all the countif results it's over 217,000! It's counting too many values. Does anyone know why?

  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 brings back too many numbers

    Perhaps a simple Pivot Table?

  3. #3
    Registered User
    Join Date
    09-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: COUNTIF brings back too many numbers

    Thanks for your reply. That won't really do what I'm after though, I need a list of values with their frequency in the other column.

  4. #4
    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 brings back too many numbers

    Perhaps post a small sample sheet showing expected results?

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: COUNTIF brings back too many numbers

    Can you give some examples of the values that return wrong results. Values containing > or < or * can cause problems in COUNTIF, try using SUMPRODUCT like this:

    =SUMPRODUCT((sheet1!$A$1:$A$3500=sheet2!A1)+0)
    Audere est facere

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: COUNTIF brings back too many numbers

    I need a list of values with their frequency in the other column.
    That's exactly what a PT will do.
    Cheers
    Andy
    www.andypope.info

+ 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. Replies: 6
    Last Post: 05-07-2013, 04:38 AM
  2. How to turn dates back to numbers
    By blockrocker in forum Excel General
    Replies: 6
    Last Post: 08-16-2011, 05:43 PM
  3. Forcing numbers stored as text back to numbers
    By Gunther Maplethorpe in forum Excel General
    Replies: 5
    Last Post: 01-19-2011, 06:02 PM
  4. [SOLVED] How to automatically add 000 at the back of my numbers?
    By devillsad in forum Excel General
    Replies: 1
    Last Post: 08-15-2006, 03:00 AM
  5. [SOLVED] my sumif statement brings back mutiple answers, how do I fix it?
    By graham in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-15-2005, 04:15 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