Results 1 to 9 of 9

How to count duplicate values only once in excel cell.

This is a discussion on How to count duplicate values only once in excel cell. within the Excel Questions forums, part of the Question Forums category; Hello MR.Excel, As you see below, I have survey result. the Problem type column has multipe answers for one question. ...

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    15

    Default How to count duplicate values only once in excel cell.

    Hello MR.Excel,
    As you see below, I have survey result. the Problem type column has multipe answers for one question. I want to count only one result out of many, such as dirty Air. when i use the countif fx, it want see the other results in the middle. Can someone help me how to count result from the column in each cell only once? Thank you.
    Problem Type
    Excessive Noise, Temp too cold
    Musty smell, Temp too cold
    Air too Dry, Dirty air
    Dirty air, Temp too cold, Too Humid
    Temp too cold
    Air too Dry, Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid
    Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid
    Dirty air, Temp too cold
    Dirty air, Musty smell, Temp too cold, Temp too hot
    Air too Dry, Dirty air, Musty smell, Temp too cold, Temp too hot

  2. #2
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,724

    Default Re: How to count duplicate values only once in excel cell.

    So based on this sample, the result should be 4?
    Regards,

    Wigi

    http://www.wimgielis.be

    Excel MVP 2011-2014

  3. #3
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: How to count duplicate values only once in excel cell.

    Quote Originally Posted by Liverpool View Post
    Hello MR.Excel,
    As you see below, I have survey result. the Problem type column has multipe answers for one question. I want to count only one result out of many, such as dirty Air. when i use the countif fx, it want see the other results in the middle. Can someone help me how to count result from the column in each cell only once? Thank you.
    Problem Type
    Excessive Noise, Temp too cold
    Musty smell, Temp too cold
    Air too Dry, Dirty air
    Dirty air, Temp too cold, Too Humid
    Temp too cold
    Air too Dry, Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid
    Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid
    Dirty air, Temp too cold
    Dirty air, Musty smell, Temp too cold, Temp too hot
    Air too Dry, Dirty air, Musty smell, Temp too cold, Temp too hot
    Maybe this...

    =COUNTIF(A2:A10,"*dirty air*")

    Or, using a cell to hold the criteria:

    C2 = dirty air

    =COUNTIF(A2:A10,"*"&C2&"*")
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  4. #4
    New Member
    Join Date
    Oct 2011
    Posts
    15

    Default Re: How to count duplicate values only once in excel cell.

    Thank you for intanct reply, however on top of that I want to show once out of 4 re****s? or how would i distinctively count only one dirty air result?
    Thank a lot

  5. #5
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,724

    Default Re: How to count duplicate values only once in excel cell.

    Maybe...

    =INDEX($A$1:$A$10,MATCH("*dirty air*",$A$1:$A$10,0))
    Regards,

    Wigi

    http://www.wimgielis.be

    Excel MVP 2011-2014

  6. #6
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: How to count duplicate values only once in excel cell.

    Quote Originally Posted by Liverpool View Post
    Thank you for intanct reply, however on top of that I want to show once out of 4 re****s? or how would i distinctively count only one dirty air result?
    Thank a lot
    Sorry, I don't understand.

    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  7. #7
    New Member
    Join Date
    Oct 2011
    Posts
    15

    Default Re: How to count duplicate values only once in excel cell.

    Sorry, I don't understand.



    What i basically need is to ditinctively count only one result out of (n resuts) displayed by the function. say 1 of out 4 Dirty Air results. Does it help?
    Thanks

  8. #8
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,245

    Default Re: How to count duplicate values only once in excel cell.

    If I follow, is this what you want:


    ABCD
    1Problem Type
    Problem TypeExists?
    2Excessive Noise, Temp too cold
    dirty airYes
    3Musty smell, Temp too cold


    4Air too Dry, Dirty air


    5Dirty air, Temp too cold, Too Humid


    6Temp too cold


    7Air too Dry, Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid


    8Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid


    9Dirty air, Temp too cold


    10Dirty air, Musty smell, Temp too cold, Temp too hot


    11Air too Dry, Dirty air, Musty smell, Temp too cold, Temp too hot



    Formula in D2 is:

    Code:
    =IF(COUNTIF(A2:A11,"*"&C2&"*"),"Yes","No")
    If you want a number displayed instead, change "Yes" to 1 and "No" to 0.

    Matty

  9. #9
    New Member
    Join Date
    Jan 2012
    Posts
    1

    Default Re: How to count duplicate values only once in excel cell.

    Check this by using a cell to hold the criteria in C1 as *dirty air*

    Write the below formula in B1
    =COUNTIF(A1,C$1$)

    Then drag this formula upto B10 to see below result

    Excessive Noise, Temp too cold

    0

    *Dirty air*
    Musty smell, Temp too cold0
    Air too Dry, Dirty air1
    Dirty air, Temp too cold, Too Humid1
    Temp too cold0
    Air too Dry, Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid1
    Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid1
    Dirty air, Temp too cold1
    Dirty air, Musty smell, Temp too cold, Temp too hot1
    Air too Dry, Dirty air, Musty smell, Temp too cold, Temp too hot

    1

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com