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

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):

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

See Sheet2 in my attachment for details.

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:

Please Login or Register  to view this content.

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?

Or try this one:

Formula:

Please Login or Register  to view this content.