Count duplicates and count duplicates based on criteria

[leterrier]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Products
[/TD]
[TD]Suppliers[/TD]
[/TR]
[TR]
[TD]doors[/TD]
[TD]Harper[/TD]
[/TR]
[TR]
[TD]windows[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]floors[/TD]
[TD]Brown[/TD]
[/TR]
[TR]
[TD]lights[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]tables[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD]chairs[/TD]
[TD]Jones[/TD]
[/TR]
[TR]
[TD]beds[/TD]
[TD]Cooper[/TD]
[/TR]
[TR]
[TD]sinks[/TD]
[TD]Jones[/TD]
[/TR]
[TR]
[TD]baths[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sofas[/TD]
[TD]Smith[/TD]
[/TR]
</tbody>[/TABLE]

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]

Dear Aladin_Ayurek,

Thank you for responding to another of my posts. I had missed your reply so apologise for being late in acknowledging. I will give this suggestion a try and let you know!

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]

.. is there a single formula I can place on the sheet that will tell me that 2 suppliers provide more than one product?

I think you'll find that Aladin's formula does exactly that.

 

[Peter_SSs]

There's possibly also these?
Both entered with Ctrl+Shift+Enter

1. If any blanks in column B are true blanks (not "" null strings)

=COUNTA(B3:B12)-SUM(IF(B3:B12="",1,1/(COUNTIF(B3:B12,B3:B12))))

2. If blanks are, or may be null strings

=COUNTIF(B3:B12,"?*")-SUM(IF(B3:B12="",1,1/(COUNTIF(B3:B12,B3:B12))))

 

[leterrier]

Peter_SSs,

Indeed it does, problem solved. Thanks so much to you both!!

I think you'll find that Aladin's formula does exactly that.

 

[leterrier]

Aladdin,

This does the trick!!!! Thank you.

Dear Aladin_Ayurek,

Thank you for responding to another of my posts. I had missed your reply so apologise for being late in acknowledging. I will give this suggestion a try and let you know!

 

[Aladin Akyurek]

Aladdin,

This does the trick!!!! Thank you.

You are welcome. By the way, the FREQUENCY version should be a tad faster.