+ Reply to Thread
Results 1 to 9 of 9

Count excluding Duplicates with criteria

  1. #1
    Registered User
    Join Date
    09-14-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    67

    Count excluding Duplicates with criteria

    Good afternoon excel community,

    Every disposition a rep makes, including non dispositions, is included in raw data I receive but on dispositions where a sale is made there is a '1' in the Z column.

    I'm trying to create a formula where I only count the first sale (sold) of each day (P Column) for each sales rep (G Column), if they made at least 1 sale of course. I also would like to create a formula in a second column where I count the # of reps who made more than 1 sale.

    To count the first sale I'm using the formula =IF(COUNTIFS($P$3:P3,P3,$G$3:G3,G3,$Z$3:Z3,Z3)=1,SUMIFS($Z$3:Z3,$P$3:P3,P3,$G$3:G3,G3),"") and filling down to the end of the data. I think I'm on the right track with that but let me know if there is an error in the logic.

    If this works, I'm thinking =IF(COUNTIFS($P$3:P9,P9,$G$3:G9,G9,$Z$3:Z9,Z9)=2,SUMIFS($Z$3:Z9,$P$3:P9,P9,$G$3:G9,G9),"") should work but I'm getting outputs of 1 and 2 in that column when I only want an output of 1 on that 2nd sale and 0 for everything else. In some instances I'm getting 1 or 2 in the 'Distinct Reps With 2+ Sales/Day' column when there isn't a 1 in the 'Distinct Reps With Sales/Day' column or 'Sold' column so I know my logic is off. Any assistance would be greatly appreciated

    MTD Rep Participation.PNG
    Attached Files Attached Files

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

    Re: Count excluding Duplicates with criteria

    Hi,
    maybe this:
    =IF(COUNTIFS($G$3:G3,G3,$P$3:P3,P3)=1,"first time","")

  3. #3
    Registered User
    Join Date
    09-14-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Count excluding Duplicates with criteria

    Hi belinda200,

    Z is the column I'm trying to get the counts of, for each distinct G and P

  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: Count excluding Duplicates with criteria

    Power Query Solution

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    1
    D2D Rep Date Submitted Count
    2
    Meek Mill
    8/5/2022
    0
    3
    Meek Mill
    8/2/2022
    0
    4
    Meek Mill
    8/1/2022
    1
    5
    Kendrick Lamar
    8/11/2022
    0
    6
    Kendrick Lamar
    8/8/2022
    2
    7
    David Banner
    8/1/2022
    3
    8
    David Banner
    8/13/2022
    0
    Sheet: Table1
    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
    Registered User
    Join Date
    09-14-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Count excluding Duplicates with criteria

    Thanks Alan,

    I've seen the power of Power Query in the past and it's definitely on my list to learn. 2 things about your response
    1) Where would I place the code you provided
    2) I'm not looking for total count, I'm looking for the count to be 1 in the 'Distinct Reps With Sales/Day' if the rep made at least 1 sale on that day and 1 in the 'Distinct Reps With 2+ Sales/Day' if the rep made at least 2 sales that day. Not looking for the actual count of sales they attained

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Count excluding Duplicates with criteria

    Try this.
    For 1 sale in BG3

    =IF(COUNTIFS($P$3:P3,P3,$G$3:G3,G3,$Z$3:Z3,1)=1,1,0)

    For more than 1 sale, in BH3

    =IF(COUNTIFS($P$3:P3,P3,$G$3:G3,G3,$Z$3:Z3,1)=1,IF(COUNTIFS($P$3:$P$13,P3,$G$3:$G$13,G3,$Z$3:$Z$13,1)>1,1,0),0)
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Registered User
    Join Date
    09-14-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Count excluding Duplicates with criteria

    The only issue here is once we get a 1 in column BG it automatically gives a 1 for every other line for that rep on that date when we should only be counting 1 sale (the first one) per date per rep.
    Added a few more lines in the sample so you can see what I mean.

    MTD Rep Participation.PNG

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Count excluding Duplicates with criteria

    In BG3

    =IF(Z3=1,IF(COUNTIFS($P$3:P3,P3,$G$3:G3,G3,$Z$3:Z3,1)=1,1,0),0)

    In BH3

    =IF(BG3=1,IF(COUNTIFS($P$3:$P$16,P3,$G$3:$G$16,G3,$Z$3:$Z$16,1)>1,1,0),0)

  9. #9
    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: Count excluding Duplicates with criteria

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

+ 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] Count Excluding Duplicates
    By grcshekar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-24-2020, 11:51 PM
  2. Count text formula based on multi-criteria excluding duplicates
    By eyeope in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-07-2019, 12:26 PM
  3. Replies: 5
    Last Post: 10-05-2018, 01:26 PM
  4. How to count using multiple criteria and excluding duplicates
    By Carayk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2018, 01:44 AM
  5. 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
  6. Pivot count excluding duplicates
    By Tom_J_W in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-02-2013, 03:14 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

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