Count distinct (remove duplicate) strings that contains a specific text in an Excel column

[wongced]

Hi,

I have a long list of texts that I need to count how many are distinct (the first occurrence), but only with only contains the word "LOOP" in the text, all the text are in 1 column. I know I can do this with remove duplicates, but I will constantly update the list, so I hope there is a way to automatically update the value.

sample of my texts:

RING1_LOOP6

RING6_LOOP4

RING5_LOOP30

RING5

RING7_LOOP40

RING1_LOOP6

RING5_LOOP30

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

so in above texts, the RING5 should not be counted, so the return value should be 4.
because, RING1_LOOP6, RING6_LOOP4, RING5_LOOP30, and RING7_LOOP40 only.


It will be very helpful if anyone can help me on this.
Thank you!

 

[kvsrinivasamurthy]

DATA in A2:A8

ARRAY Formula

=SUM(IFERROR(1/(COUNTIF($A$2:$A$8,IF(ISNUMBER(FIND("LOOP",$A$2:$A$8)),$A$2:$A$8,"ZZZZZZ"))),0))

ARRAY formula is used


To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

 

[bosco_yip]

1] Assume your data put in A2:A8

2] B2, enter formula :

=SUMPRODUCT(ISNUMBER(SEARCH("LOOP",A2:A8))/COUNTIF(A2:A8,A2:A8))

Regards
Bosco

 

[wongced]

I have tried something like this, but it doesn't work.
I will try yours again.

Thank you!

 

[wongced]

Thank you for your help!
I will also try on this one!