# 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. ## 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. ## Re: How to count duplicate values only once in excel cell.

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

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

Originally Posted by Liverpool
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&"*")

4. ## 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. ## 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))

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

Originally Posted by Liverpool
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.

7. ## 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. ## Re: How to count duplicate values only once in excel cell.

If I follow, is this what you want:

 A B C D 1 Problem Type Problem Type Exists? 2 Excessive Noise, Temp too cold dirty air Yes 3 Musty smell, Temp too cold 4 Air too Dry, Dirty air 5 Dirty air, Temp too cold, Too Humid 6 Temp too cold 7 Air too Dry, Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid 8 Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid 9 Dirty air, Temp too cold 10 Dirty air, Musty smell, Temp too cold, Temp too hot 11 Air 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. ## 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 cold 0 Air too Dry, Dirty air 1 Dirty air, Temp too cold, Too Humid 1 Temp too cold 0 Air too Dry, Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid 1 Dirty air, Musty smell, Noticeable Odors, Temp too hot, Too Humid 1 Dirty air, Temp too cold 1 Dirty air, Musty smell, Temp too cold, Temp too hot 1 Air too Dry, Dirty air, Musty smell, Temp too cold, Temp too hot 1

#### Posting Permissions

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