Count non-unique items from list of unique items

[GrantP]

Hi Everyone,

This is my first post and I am also very new to Excel so please forgive me if this is a silly question. I have a list of some 300+ plus items each one is unique but only because of the last 3 characters

EG

Cars S1
Bikes S1
Lorry S1
Cars S2
Cars S3
Bikes S2
Trailers S1

I want to be able to get the result 4 from this list as there are 4 modes of transport. Cars/Bikes/Lorry/Trailers

I don't want to put the 'S1,S2,S3 etc.' identifiers in another cell.

I found this in the forum that works perfect if I remove the 'S' identifiers but I can't do that.

=SUMPRODUCT((E4:E342<>"")/COUNTIF(E4:E342,E4:E342&""))


I hope this makes sense. Any help would be greatly appreciated.

Many Thanks Grant

 

[GrantP]

I eventually sorted it as follows

{=SUM(IF(FREQUENCY(IF(LEN(H4:H13)>0,MATCH(LEFT(H4:H13,(LEN(H4:H13)-3)),LEFT(H4:H13,(LEN(H4:H13)-3)),0),""), IF(LEN(H4:H13)>0,MATCH(LEFT(H4:H13,(LEN(H4:H13)-3)),LEFT(H4:H13,(LEN(H4:H13)-3)),0),""))>0,1))}

But if someone knows a better/easier way as this occurs over 70 times I would appreciate some help

Thanks Grant