Counting number of similar entries

Here's what I'm trying to do. I have a spreadsheet which contains the serial numbers, asset IDs, and other information for the radios we have in our inventory. I currently use this sheet to populate other sheets automatically when a serial number is entered. This sheet contains multiple models of radios and I'm looking for a way to count how many of each radio we have without having to do it manually. The format for these serial numbers is 123ABC4567. The first 3 numbers are the same for each specific model of radio. For example, if I see a serial number of 205CDY3245, I know it is a XTS2500 portable but if the number is 527CMM4838, I know it is an APX6500 mobile. There are around 20 different models that we have.

Basically, I want to have a formula that tells me I have x number of XTS2500s, x number of APX6500s, etc.... I know I can do this by creating a column for each model type and putting a 1 in for that specific model, but that is time consuming and a mistake could easily be made. I don't know enough about formulas to figure out how to look at just the first 3 digits of the serial number and count those that match what I'm looking for. (i.e. looking for XTS2500s that start with 205XXX#### and count just those.)

Any help would be greatly appreciated. Thanks in advance.

Wayne

You could try using the MID formula to get the first 3 digits

=MID(A1,1,3)

Then you can use VLOOKUP to get the model name from a list range and store this in a new column. Once it's stored, you can use this in COUNTIF formulas or pivot tables.

So if your model name range was "Models", with the 3 digit code in the first column and the model name in the second, it might look something like:

=VLOOKUP(MID(A1,1,3),Models,2,false)

If the 3 digit code in the first column is always a number, you will need to convert the MID result to a value so it would look like:

=VLOOKUP(VALUE(MID(A1,1,3)),Models,2,false)

Hope that helps
Shirley