Count specific text in a colum but i want it to count duplicates only Once

[mat127]

"Hey Guys im new here, I have a formula that i want to do and for some reason i wasnt able to get a clear answer anywhere. My goal is to be able to count the number of specific text in a colum but i want the duplicates to be only counted once. I hope someone can help me

Attached is my example:

Basically i wan to count the number of challenger products that are in the colum but i only want to count the duplicates as one count. Aslo : I want it to be specific text aka 'Challenger 350"" since there are other types of challengers. (Please note that the duplication is due to the last 5 digits (sERIAL NUMBERs) "
Book1.xlsx

[Olly]

I would add a couple of helper columns :

- one to separate out the model (e.g. CHALLENGER 350) from the serial number (in Column D):

=LEFT(C3,FIND("#",SUBSTITUTE(C3," ","#",LEN(C3)-LEN(SUBSTITUTE(C3," ",""))))-1)

- one as the reciprocal of the count of matching values, to allow a distinct count (in Column E):

=1/COUNTIF($C$3:$C$41,C3)

Then use a SUMIF, to count the distinct values for each model.

=SUMIF($D$3:$D$41,"CHALLENGER 350",$E$3:$E$41)

See Sheet2 in my attachment for details.

[Ron Coderre]

Using your posted workbook
and
I2: CHALLENGER 350
I3: CHALLENGER 360
This regular formula returns the count of unique values prefixed by the referenced cell:

H2: =SUMPRODUCT(ISNUMBER(--TRIM(SUBSTITUTE(UPPER($C$3:$C$50),UPPER(I4),"")))*($C$3:$C$50<>""),
--(MATCH($C$3:$C$50&"",$C$3:$C$50&"",0)=ROW($C$3:$C$50)-2))

Copy that formula into H3

For your data, those formulas return:
16 <-I think your count was off by one
6

Note: that formula works as long as the last characters are numeric.

Is that something you can work with?

[Tsjallie]

Or try this one:

=SUM(IF(LEFT(C3:C41,14)="CHALLENGER 350",1 / COUNTIF(C3:C41,C3:C41),0))