Help! How to do a complex Count in Excel

[abra]

Below is an example table.

What I am after is for a excel forumula to count the number of instances that a item is unique in a set of three as below;

Here is the "data/source" table:

Book3.xls
	A	B	C	D
1	ProductID	SupplierName	Quantity1-10	PriceRank
2	1	ABC	0.00	Low
3	1	DEF	54.60	Middle
4	1	GHI	156.00	High
5	2	ABC	0.00	Low
6	2	DEF	0.00	Middle
7	2	GHI	156.00	High
8	3	GHI	0.00	Low
9	3	ABC	0.00	Middle
10	3	DEF	156.00	High
11	4	DEF	0.00	Low
12	4	ABC	0.00	Middle
13	4	GHI	156.00	High
Sheet1

And here is how I want the output table to be:

Book3.xls
	D	E	F	G
16	Supplier	NumberofProductsbeingthesolesupplier
17	ABC	0
18	DEF	1
19	GHI	2
20	etc	etc
Sheet1

So that for each supplier, the excel forumula calculates how many products are unique.

If a supplier has "£0.00" in the quantity band it would assume they do not supply that product.

Your input would be really appreciated.

 

[Domenic]

Thanks, it works perfect now. Wish I had the same Excel skills as you.

You're very welcome!

 

[abra]

Dom, another Q related to this.

Is there an easy way for Excel to list out the sole Suppliers related to each Product ID,

e.g. in this case it would state that Supplier GHI is the only stockist for Product ID 4 and Product ID 2.

Many thanks,

 

[Domenic]

Dom, another Q related to this.

Is there an easy way for Excel to list out the sole Suppliers related to each Product ID,

e.g. in this case it would state that Supplier GHI is the only stockist for Product ID 4 and Product ID 2.

Many thanks,

The following is resource-intensive. So if the amount of data is large, you'll find the calculations too slow. If this is the case, a different approach will be necessary. Probably one involving helper columns. Also, if you have more than 251 unique products, we'll need to transpose the orientation/layout. Having said that, based on the prior results in E17, E18, E19, etc., try...

1) Select F17

2) Define (Insert > Name > Define) the following...

Name:  Array1

Refers to:

=ISNUMBER(MATCH(IF($C$2:$C$13>0,$A$2:$A$13,"#"),IF($B$2:$B$13=$D17,IF($C$2:$C$13>0,$A$2:$A$13)),0))

Click Add

Name:  Array2

Refers to:

=MATCH($A$2:$A$13,$A$2:$A$13,0)

Click Add

Name:  Array3

Refers to:

=ROW($A$2:$A$13)-ROW($A$2)+1

Click Ok

3) Try...

F17, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(COLUMNS($F17:F17)<=$E17,INDEX($A$2:$A$13,SMALL(IF(FREQUENCY(IF(Array1,Array2),Array3)=1,Array3),COLUMNS($F17:F17))),"")

Hope this helps!

 

[abra]

Thanks Dom. No idea how you come up with these solutions soo quickly. I waas racking my brains for ages.