+ Reply to Thread
Results 1 to 3 of 3

Count Duplicates In Table - Can't use COUNTIF

  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    New Haven, Ct
    MS-Off Ver
    Excel 2016
    Posts
    41

    Exclamation Count Duplicates In Table - Can't use COUNTIF

    Hello All!

    I have been completely stumped by this challenge: I have a two column table: col A is a first name, col B is a date. The table is formatted as an excel table. Per the rules of this challenge I am unable to do any of the following:

    1. I can't change any of the data IN the table itself
    2. I HAVE to use a formula (no pivot tables, power pivot, VBA, etc.)
    3. I can't use the COUNTIF/COUNTIFS formulas

    What I need to do is find how many instances of the combination of CONCATENATE(ColumnA, MONTH(ColumnB)) exist in the table. I added a col C to concatenate col A and the month of Col B so that I would have a static reference to bump against the two table columns. I also added a col D to perform the count for each concatenation. The output that I expect is:
    Name DOB Concat Count
    Richard 7/1/88 Richard7 2
    Patty 2/18/58 Patty2 1
    Paul 9/6/56 Paul9 1
    Paul 10/01/67 Paul10 1
    Raymond 4/23/50 Raymond4 1
    Richard 3/21/44 Richard3 1
    Richard 7/9/65 Richard7 2

    As you can see "Richard7" has a count of 2 on line 1 and line 7 because each time it searches for "Richard7" it finds itself, and then another occurence of itself. The remaining lines have a count of 1 because each search only finds itself.

    I have tried a lot of different angles with SUM/IF/MATCH, DCOUNTA, SUMIFS, etc. and I just can't get this to work. The ONLY formula that I have managed to get to work is =COUNTIF($C$3:$C$77,Table1[@Name]&MONTH(Table1[@DOB])), but of course I can't use this as it breaks rule #3. I have attached a sample of the table for reference. Any help would be IMMENSELY appreciated.
    Attached Files Attached Files
    Last edited by Justin H2O; 03-25-2021 at 01:55 PM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Count Duplicates In Table - Can't use COUNTIF

    Hi,
    how about this in D4 and down:
    =SUM(--(C4=Table1[Name]&MONTH(Table1[DOB])))

  3. #3
    Registered User
    Join Date
    12-30-2010
    Location
    New Haven, Ct
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Count Duplicates In Table - Can't use COUNTIF

    Just solved my own issue.
    Please Login or Register  to view this content.
    It's amazing what happens when I take a second to rubber duck it.

+ 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. Count of duplicates in a table
    By WilliamWelch in forum Excel General
    Replies: 4
    Last Post: 03-15-2020, 03:26 PM
  2. excel 2010 -a faster solution to count: pivot table or countif, which one?
    By seraphin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-28-2018, 03:16 PM
  3. [SOLVED] Default count how many times a value is changed in a table ignoring duplicates
    By galgo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-26-2017, 02:19 AM
  4. [SOLVED] Countif with 2 conditions (Ignore EEE, Count duplicates as 1)
    By Sekars in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2017, 09:34 AM
  5. Replies: 3
    Last Post: 10-01-2015, 11:09 AM
  6. Replies: 0
    Last Post: 04-05-2010, 03:38 PM
  7. Replies: 1
    Last Post: 01-16-2006, 02:20 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