+ Reply to Thread
Results 1 to 9 of 9

Counting Duplicates in 1 formula

  1. #1
    Registered User
    Join Date
    03-16-2020
    Location
    New york
    MS-Off Ver
    2017
    Posts
    2

    Counting Duplicates in 1 formula

    I need a formula that counts duplicates in a specific column and returns the number of duplicates. For instance: I need the formula to go through column be and tell me that there are 2 values that are duplicated. Does not matter how many times they are duplicated, just need it to return the value of 2.

    Column B:
    12312
    12318
    12392
    19232
    12312
    16782
    12312
    12318

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    711

    Re: Counting Duplicates in 1 formula

    There are more ways, here a few:

    Cheers
    Erwin
    Attached Files Attached Files
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,220

    Re: Counting Duplicates in 1 formula

    Assuming values in A2:A9 how about
    =SUM((COUNTIF(A2:A9,A2:A9)>1)-(COUNTIF(A2:A9,A2:A9)=1))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  4. #4
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    711

    Re: Counting Duplicates in 1 formula

    or in 1 formula:
    Please Login or Register  to view this content.
    gives the max amount of occurrences, the IF function reduces it to 2

    Cheers
    Erwin

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,220

    Re: Counting Duplicates in 1 formula

    Ignore the formula in post#3 it doesn't work

  6. #6
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    711

    Re: Counting Duplicates in 1 formula

    This one is doing too the job, but if the array has only unique numbers, the COUNTIF returns 0 (Anyway, you were not interested in that:
    Please Login or Register  to view this content.
    Personally, I would go for the formula in Post #4 , that is more accurate.

    Cheers
    Erwin
    Last edited by Eastw00d; 03-27-2020 at 01:50 PM. Reason: minor update

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,702

    Re: Counting Duplicates in 1 formula

    Another way. Also Array entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,220

    Re: Counting Duplicates in 1 formula

    @Eastw00d
    I think the OP wants to now how many distinct values are duplicated, the number of times they occur are irrelevant.
    You're formulae in posts 4 & 6 just return either 2 or 0, regardless of the data.

  9. #9
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    711

    Re: Counting Duplicates in 1 formula

    Maybe you are right; I read this, but I am not native English:
    Does not matter how many times they are duplicated, just need it to return the value of 2.
    For me I can see this only logical

    Lets wait what the OP is telling....

    Cheers
    Erwin

+ 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/ifs formula but not counting duplicates
    By vito11 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2017, 09:58 AM
  2. Formula or Macro for counting duplicates
    By razmas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2017, 07:49 AM
  3. Formula for Counting Data with Duplicates
    By tentson in forum Excel General
    Replies: 8
    Last Post: 05-07-2016, 01:34 AM
  4. Count formula without counting duplicates
    By jason4444 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-14-2016, 10:07 PM
  5. [SOLVED] Counting Matching values in two separate ranges without counting duplicates
    By Rhall6310 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-01-2014, 03:35 PM
  6. counting cells with data without counting duplicates
    By labettis in forum Excel General
    Replies: 2
    Last Post: 11-05-2007, 12:10 PM
  7. counting duplicates within array formula?
    By etmac in forum Excel General
    Replies: 5
    Last Post: 09-05-2006, 04:45 AM

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