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

#### leterrier

##### New Member
Any ideas anyone? Thanks

#### Peter_SSs

##### MrExcel MVP, Moderator
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

#### Peter_SSs

##### MrExcel MVP, Moderator
... 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)

#### leterrier

##### New Member
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!

#### liveinhope

##### Well-known Member
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>

##### MrExcel MVP
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))

#### Peter_SSs

##### MrExcel MVP, Moderator
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

#### leterrier

##### New Member
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!

#### leterrier

##### New Member
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