# Count the number of unique values in a column when a condition from another column is met

1. ## Count the number of unique values in a column when a condition from another column is met

Hi this is my first post. I have recently landed a job where i deal with excel a lot. I am preparing a report and i am unable to write a formula for counting some unique values.
here is my dilemma.
In colunm 'A' i have some repeating values and in column 'B' I have the 'type' for Column A's values. I want to write a formula where it checks the 'type' first and them count the number of unique values in colums 'A'

Coumn A Column B
Pr1 Soap
Pr1 Soap
Pr1 Soap
Pr2 Food
Pr2 Food
Pr3 Cloth
Pr4 Cloth
Pr5 food

so the formula should count the unique number of products in column A by checking its type in column B when I prepare a table of the number of products based on type.
this should be my result
Soap Food Cloth
1 2 2

2. ## Re: Count the number of unique values in a column when a condition from another column is

Advance appologies if my question is too confusing. I can do this by sorting the products by type and then using this formula SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) but there is risk of the report going wrong if someone were to insert a row in the existing table.

3. ## Re: Count the number of unique values in a column when a condition from another column is

Hello
If you're happy to use array formulas (entered with CTRL+SHIFT+ENTER), then try:

``Please Login or Register  to view this content.``
Here your sample data is in the range A2:B9. The hard coded "Soap" could be changed to a cell reference.

Here is link to a web page that might be useful:

http://www.get-digital-help.com/2011...eria-in-excel/

Hope this helps.
DBY

4. ## Re: Count the number of unique values in a column when a condition from another column is

It workes absolutely fine
Thank you very much

5. ## Re: Count the number of unique values in a column when a condition from another column is

Non-array perhaps..

=SUMPRODUCT((\$B\$2:\$B\$9="Soap")/(COUNTIFS(\$B\$2:\$B\$9,\$B\$2:\$B\$9,\$A\$2:\$A\$9,\$A\$2:\$A\$9)))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1