Ok I have this spread sheet Client types of products

X A

B

C

Y B

C

B

B

so onI 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

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

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.

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

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.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History