# Count excluding Duplicates with criteria

1. ## 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

2. ## Re: Count excluding Duplicates with criteria

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

3. ## 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. ## 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 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

5. ## 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. ## 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)

7. ## 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. ## 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. ## 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 a video which demonstrates how to use Power Query code provided.

There are currently 1 users browsing this thread. (0 members and 1 guests)