+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    07-16-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    3

    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
    Last edited by adityae; 07-16-2013 at 06:51 AM.

  2. #2
    Registered User
    Join Date
    07-16-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    3

    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. #3
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    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. #4
    Registered User
    Join Date
    07-16-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    3

    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. #5
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    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)))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 05-15-2013, 04:50 PM
  2. [SOLVED] Macro to count unique values in a column, enter it in next column, then delete duplicates
    By pmorisse in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2013, 03:27 PM
  3. Replies: 11
    Last Post: 01-18-2013, 04:05 PM
  4. [SOLVED] Count Unique Values in Column A that meet criteria in Column B
    By ashakespeare in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-04-2012, 07:33 PM
  5. Replies: 12
    Last Post: 07-03-2012, 04:50 PM

Bookmarks

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