Excel to count types ignore repeats

February 11, 2011 at 12:05:52
Specs: Windows XP
Ok I have this spread sheet

Client types of products
X A
B
C
Y B
C
B
B
so on

I need to know the number of different products a certain company has, regardless of repeats. How can I do this in excel
For example, Company X would have 3 types
Company Y would have 2


See More: Excel to count types ignore repeats

Report •

#1
February 11, 2011 at 12:22:29
To be more clear, column A had client name Column B had product

Client Product
X A
X B
X C
Y B
Y C
Y B
Y B


Report •

#2
February 11, 2011 at 12:44:50
This formula will count the number of unique values within the referenced range.

It will count both text and numbers but can not contain any blank cells.

=SUM(IF(FREQUENCY(MATCH(B1:B3,B1:B3,0),MATCH(B1:B3,B1:B3,0))>0,1))

I suggest you name your ranges to make using the formula easier.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 11, 2011 at 13:56:17
Ok, so now I made my sheet look like this

Column A has raw data of client name appearing multiple times
Column B has Product bought by that client

Basically showing every time client bought something regardless of product is it logged.

I went through and created Column D which only has the client name listed once. (which turns out I only have 244 clients)

In column E, I would like to know how many different types of product did a particular company buy.
I do not need to know how many of each product they bought.

I cannot seem to follow what you did in that equation. In the end, I just need to know Company X buys 2 types of product
Company Y buys 1 type
Company Z buys 5 types
etc down the list


Report •

Related Solutions

#4
February 11, 2011 at 16:18:21
There is something missing from your latest post which makes it difficult to answer your question.

I've checked, but I can't seem to find where you said anything that resembles "Thanks for helping me with this."

Did I miss it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question