+ Reply to Thread
Results 1 to 6 of 6

Formula that counts duplicates on a separate tab

  1. #1
    Registered User
    Join Date
    01-20-2023
    Location
    Colorado
    MS-Off Ver
    Office 365
    Posts
    3

    Formula that counts duplicates on a separate tab

    Good morning!

    Please see attached.

    Is there any way to have the formula in cell B3 on the USE tab count the duplicates that exist within columns B and C on the SITES tab, depending on what value is in cell B1 on the USE tab?

    Essentially a formula that provides the answer to: Whatever value exists in tab USE: cell B1, count duplicates within tab SITES: columns B and C which consist of that value.

    Thank you for your help.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,581

    Re: Formula that counts duplicates on a separate tab

    A formula that doesn't work.... one point. An expected answer, calculated maually, 10 points.

    So... a guess:

    =COUNTA(FILTER(SITES!B2:B26,SITES!B2:B26=USE!B1))
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    01-20-2023
    Location
    Colorado
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Formula that counts duplicates on a separate tab

    Ok, edited.

    So the formula is supposed to count the duplicate addresses in column C, that correspond with the city in column B. Can that be done?

    For instance:

    Oakland 150 is listed 4 times. This should be 4 of the duplicates counted.
    Last edited by Danger_SF; 01-20-2023 at 01:30 PM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,581

    Re: Formula that counts duplicates on a separate tab

    No. Please upload FULL expected answers for a RANGE of scenarios.

    This: "Oakland 150 is listed 4 times. This should be 4 of the duplicates counted." does not tell me if 4 is the final answer... or part of the final answer....

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    10,646

    Cool Re: Formula that counts duplicates on a separate tab

    just for fun with Power Query
    (tailored to your example)
    sandy
    Learn Power Query (XL2010/2013 require PQ add-in)
    I want to see what you are trying to achieve, not how you are trying to do it
    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  6. #6
    Registered User
    Join Date
    01-20-2023
    Location
    Colorado
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Formula that counts duplicates on a separate tab

    Thank you. I'll try to read up on this particular problem. I need to educate myself. Thanks again.

+ 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] Remove duplicates from dynamic row counts
    By tvanduzee in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2022, 05:34 AM
  2. Filtering and Total counts of duplicates from multiple columns
    By kor123 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-23-2016, 11:55 PM
  3. Replies: 2
    Last Post: 04-21-2015, 03:54 PM
  4. Formula that counts everything except duplicates...
    By mikeross in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-22-2014, 02:13 AM
  5. Replies: 2
    Last Post: 12-27-2013, 09:24 AM
  6. [SOLVED] Help! How do I combine duplicates with different counts of inventory?
    By tybles in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2013, 09:25 PM
  7. Ignore Duplicates in Counts
    By dfq in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2007, 03:11 AM

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