(Simple) Unique Count - Formula problem

Hi,

I'm having some difficulties creating a unique count formula. I am trying to look-up a supplier name against a data extract, and count the number of Categories they fall under.

For example, the 2 fields I'm concerned with on the look-up table are:

A B
Supplier | Category

A1| Extreme Sport | Event Services
A2| Extreme Sport | Media Production
A3| Extreme Sport | Media Production
A4| One Media | Media Production


IE. Extreme Sport would fall under 2 categories (Event Services & Media Production). On my report I need it to look like this:


A B
Supplier | Number of Categories

A1| Extreme Sport | 2
A2| One Media | 1


However, I can't seem to find a way to use a COUNTIF that doesn't return the number lines, rather than the number of unique values. I was thinking about a INDEX/MATCH but imagine it will have the same problem.

Any help would be greatly appreciated!

Cheers
Alan

Here is some code from Jerry B's site which wiil consolidate and merge making it easy for you to perform a count

Please Login or Register  to view this content.

Are you looking for distinct or unique values?

to get unique list for suppliers

**Array formula in C2 and copy down

=IFERROR(INDEX($A$2:$A$100,MATCH(0,IF(ISBLANK($A$2:$A$100),"",COUNTIF(C$1:$C1, $A$2:$A$100)), 0)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

For count unique categories:

In D2 and copy down

=SUMPRODUCT(($A$2:$A$5=$C2)/COUNTIFS($A$2:$A$5,$A$2:$A$5,$B$2:$B$5,$B$2:$B$5&""))

Row\Col	A	B	C	D
1	Supplier	Category	Unique Supplier Name	Count
2	Extreme Sport	Event Services	Extreme Sport	2
3	Extreme Sport	Media Production	One Media	1
4	Extreme Sport	Media Production
5	One Media	Media Production