# Count duplicates and count duplicates based on criteria

#### leterrier

##### New Member
 Products Suppliers doors Harper windows Smith floors Brown lights Smith tables Johnson chairs Jones beds Cooper sinks Jones baths sofas Smith

<tbody>
</tbody>

Friends, I again request your assistance.

I have 2 columns of sales data, A and B. Range A3:A12 contains unique product names, range B3:B12 contains non-unique (possibly blank) supplier names.

I wish to know how to construct formulas to tell me;

i) how many suppliers provide more than one product i.e. how many duplicates are in col B? The answer is 2 (Smith and Jones)
and;

ii) for each of these duplicates, how many products do they supply? i.e how many times does each duplicate supplier appear? The answers are Smith = 3, Jones = 2.

Thank you sincerely in anticipation.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Any ideas anyone? Thanks

If you are using Excel 2010 or later, try these two formulas, copied down.

Excel Workbook
ABCDE
1ProductsSuppliersMultiplesCount
2doorsHarperSmith3
3windowsSmithJones2
4floorsBrown
5lightsSmith
6tablesJohnson
7chairsJones
8bedsCooper
9sinksJones
10baths
11sofasSmith
12
Duplicates

... alternatively, if you don't mind using a helper column.

C1 is blank or houses a zero.

C2:E2 copied down

Excel Workbook
ABCDE
1ProductsSuppliersMultiplesCount
2doorsHarper0Smith3
3windowsSmith1Jones2
4floorsBrown1
5lightsSmith1
6tablesJohnson1
7chairsJones2
8bedsCooper2
9sinksJones2
10baths2
11sofasSmith2
12
Duplicates (2)

Dear Peter_SSs,

Thank you so much for responding! I must apologise, I did not make myself clear, I need the formula to count rather than list the number of duplicates. Can I have a formula show "2"" as a count for Smith (1) and Jones (1)? has again!

Another way to do this is use a pivot table instead of formulas - easier to understand what you are doing

row field = supplier , value field count of product . You can filter the result to show only supplies with more than 1 product

 Row Labels Count of Products Brown 1 Cooper 1 Harper 1 Johnson 1 Jones 2 Smith 3 (blank) 1 Grand Total 10
<colgroup><col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3328;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <tbody> </tbody>

Any ideas anyone? Thanks

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B3:B12<>"",MATCH(B3:B12,B3:B12,0)),ROW(B3:B12)-ROW(B3)+1)>1,1))

Dear Peter_SSs,

Thank you so much for responding! I must apologise, I did not make myself clear, I need the formula to count rather than list the number of duplicates.
ii) for each of these duplicates, how many products do they supply? i.e how many times does each duplicate supplier appear? The answers are Smith = 3, Jones = 2

I hadn't answered your first request but it is a simple consequence of the second. For either of my previous suggestions just add as below.
(Note that I have now moved data down a row to match your original description.)

Excel Workbook
ABCDEF
1
2ProductsSuppliersMultiplesCountNo. of Multiple Names
3doorsHarperSmith32
4windowsSmithJones2
5floorsBrown
6lightsSmith
7tablesJohnson
8chairsJones
9bedsCooper
10sinksJones
11baths
12sofasSmith
13
Duplicates

Dear Livinhope,

Thank you for responding For this task a formula is required but thanks for the suggestion, I will make a point of coming back to pivot tables for my next project. Thanks again!

Dear Peter_SSs,

Thank you again for coming back. Yes, you have indeed answered my questions despite my lack of clarity. If I could ask your indulgence once more, is there a single formula I can place on the sheet that will tell me that 2 suppliers provide more than one product? Though I can make it work with the help you have kindly provided this particular task is really looking for a one ell / formula reply - if that is feasible. Thank you for taking the time to help out a novice.

I hadn't answered your first request but it is a simple consequence of the second. For either of my previous suggestions just add as below.
(Note that I have now moved data down a row to match your original description.)

Duplicates

 A B C D E F 1 2 Products Suppliers Multiples Count No. of Multiple Names 3 doors Harper Smith 3 2 4 windows Smith Jones 2 5 floors Brown 6 lights Smith 7 tables Johnson 8 chairs Jones 9 beds Cooper 10 sinks Jones 11 baths 12 sofas Smith 13

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 86px;"><col style="width: 80px;"><col style="width: 24px;"><col style="width: 79px;"><col style="width: 80px;"><col style="width: 149px;"></colgroup><tbody>
</tbody>

 Cell Formula F3 =COUNT(E3:E12)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Replies
4
Views
615
Replies
5
Views
315
Replies
8
Views
4K
Replies
1
Views
982
Replies
0
Views
868

1,221,122
Messages
6,158,062
Members
451,462
Latest member
fpt264

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back