+ Reply to Thread
Results 1 to 5 of 5

How to count using multiple criteria and excluding duplicates

  1. #1
    Registered User
    Join Date
    05-07-2018
    Location
    Boston, US
    MS-Off Ver
    2016
    Posts
    2

    How to count using multiple criteria and excluding duplicates

    Hi, I am having trouble pulling counts from a large dataset.
    I am setting multiple criteria and only want to count unique entries once (excluding any duplicates that match criteria).

    Example of data:

    ID Order
    23343 Y
    932 Y
    2384792 N
    984 Y
    238139 N
    23343 Y
    23343 N


    So, I want to count the total number of unique entries here excluding dupes, which would be 6 not 7

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: How to count using multiple criteria and excluding duplicates

    Just delete duplicates based on the ID and Order columns being the same then do a count.

  3. #3
    Registered User
    Join Date
    05-07-2018
    Location
    Boston, US
    MS-Off Ver
    2016
    Posts
    2

    Re: How to count using multiple criteria and excluding duplicates

    I want this to be reproducible for other data sets, so cannot delete duplicates. Looking for a formula that would allow me to reference entire columns and count based on criteria.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to count using multiple criteria and excluding duplicates

    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: How to count using multiple criteria and excluding duplicates

    May try

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    F
    1
    ID Order
    2
    23343
    Y
    6
    3
    932
    Y
    4
    2384792
    N
    5
    984
    Y
    6
    238139
    N
    7
    23343
    Y
    8
    23343
    N
    Sheet: Sheet1

    E2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

+ 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] Lookup to match multiple criteria and return multiple results excluding duplicates.
    By kris3012 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-13-2018, 11:06 AM
  2. Count from a text list, excluding duplicates, with multiple criteria
    By Ecervantes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2017, 10:42 PM
  3. [SOLVED] Count with multiple criteria but duplicates only once
    By arodasr in forum Excel General
    Replies: 17
    Last Post: 07-20-2015, 03:34 AM
  4. Pivot count excluding duplicates
    By Tom_J_W in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-02-2013, 03:14 AM
  5. Replies: 2
    Last Post: 12-02-2011, 03:42 PM
  6. Count first entries (ignore duplicates) against multiple criteria
    By Bazza in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2008, 11:44 AM
  7. [SOLVED] Count excluding Duplicates
    By GRM via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-15-2005, 05:10 PM

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