# Help! How to do a complex Count in Excel

#### abra

##### New Member
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
ABCD
1ProductIDSupplierNameQuantity1-10PriceRank
21ABC0.00Low
31DEF54.60Middle
41GHI156.00High
52ABC0.00Low
62DEF0.00Middle
72GHI156.00High
83GHI0.00Low
93ABC0.00Middle
103DEF156.00High
114DEF0.00Low
124ABC0.00Middle
134GHI156.00High
Sheet1

And here is how I want the output table to be:
Book3.xls
DEFG
16SupplierNumberofProductsbeingthesolesupplier
17ABC0
18DEF1
19GHI2
20etcetc
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.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### gecs

##### Active Member
First of all I think you need to reorganize your data. Something like the example below will be better, in my opinion. Using the data in column E (counting the number of suppliers by product) will help you then to achieve your goal using the formulas in cells B6, C6, D6.
Excel Workbook
ABCDE
1*ABCDEFGHINo. of Suppliers
210.0054.60156.002
320.000.00156.001
430.00156.000.001
540.000.00156.001
6Unique suppliers012*
Sheet2

</body></html>

#### Domenic

##### MrExcel MVP
Try...

E17, confirmed with CONTROL+SHIFT+ENTER, and copied down:

Code:
``=SUM(IF(FREQUENCY(IF(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)),\$A\$2:\$A\$13),\$A\$2:\$A\$13)=1,1))``
Hope this helps!

#### abra

##### New Member
Guys, thanks for your replies, however the forumulas do not work specificaly

=SUM(IF(FREQUENCY(IF(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)),\$A\$2:\$A\$13),\$A\$2:\$A\$13)=1,1))

returns 0 for all instances while "0" should only be for supplier ABC.

I would be grateful for more assistance as I can not workout a solution.

Thanks.

#### gecs

##### Active Member
I already gave you a solution. Works fine, but if you're waiting for "miracles", what can I say...

#### mortgageman

##### Well-known Member
Guys, thanks for your replies, however the forumulas do not work specificaly

=SUM(IF(FREQUENCY(IF(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)),\$A\$2:\$A\$13),\$A\$2:\$A\$13)=1,1))

returns 0 for all instances while "0" should only be for supplier ABC.

I would be grateful for more assistance as I can not workout a solution.

Thanks.

I haven't checked - but are you sure you are using Dom's formula as a CSE?<CTRL><SHIFT><ENTER><ENTER>

#### Domenic

##### MrExcel MVP
Guys, thanks for your replies, however the forumulas do not work specificaly

=SUM(IF(FREQUENCY(IF(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)),\$A\$2:\$A\$13),\$A\$2:\$A\$13)=1,1))

returns 0 for all instances while "0" should only be for supplier ABC.

I would be grateful for more assistance as I can not workout a solution.

Thanks.

Make sure that the ID numbers are being recognized as true numerical values. For example, the following formula should return TRUE...

=ISNUMBER(A2)

Or are the ID numbers actually made up of alpha-numerical characters?

#### abra

##### New Member
Dom -

1. They are alpha numeric (numbers seperated by a /) e.g. 1234/4567 so I have mislead you. Is there a solution?

(If I use your formula below, and use numeric values with the example posted it returns incorrect answers i.e. it returns GHI = 1 while it should equal 2 being the sole supplier for products 2 and 4)

#### Domenic

##### MrExcel MVP
Dom -

1. They are alpha numeric (numbers seperated by a /) e.g. 1234/4567 so I have mislead you. Is there a solution?

In that case, try the following instead...

Code:
``=SUM(IF(FREQUENCY(IF(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)),MATCH(\$A\$2:\$A\$13,\$A\$2:\$A\$13,0)),ROW(\$A\$2:\$A\$13)-ROW(\$A\$2)+1)=1,1))``

(If I use your formula below, and use numeric values with the example posted it returns incorrect answers i.e. it returns GHI = 1 while it should equal 2 being the sole supplier for products 2 and 4)

Based on the sample data, the formula returns the same posted results. Make sure that the suppliers entered in D17, D18, D19, etc., match exactly with their corresponding values in Column B and don't have spelling mistakes, extra spaces, etc.

Hope this helps!

#### abra

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

Replies
1
Views
287
Replies
15
Views
2K
Replies
11
Views
388
Replies
0
Views
308
Replies
4
Views
250

1,191,482
Messages
5,986,838
Members
440,052
Latest member
silverandcoldmc

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