Excel question, counting unique text values in a column

Factual Questions
1

OK, I’ve got a (possibly) quick Excel question on a spreadsheet I’m working with.

Spreadsheet has a column listing serial numbers, which are generally combinations of letters and numbers but can also be numbers only or letters only (also can include special charecters like dashes, asteriks, etc.). Serial numbers can appear more than once in the list, or just once too. Blank cells are in the column as well.

Is there a way for me to count the number of different serial numbers in the list, regardless of how many times they appear?

Thanks.

2

The Countif function.

=Countif(range,<serial number>). Put the numbers in quotes if they have letters in them.

3

Thanks for the help, but I don’t think I explained my question well, or I am misunderstanding the countif function. I thought the countif would tell me how many times a specific value appears in the list, and I’d have to specify the value I’m looking for. That’s not exactly what I’m trying to do, and I’m really trying to find out how many different values are in the list. For example, if I had the list:

123456
abcdef
123abc
123abc
abc123
123abc

I’d be looking for the answer to be 4 (since 123abc appears three times), even though there are actually 6 values in the list.

4

Don’t know if you can manipulate the data or not, but if you can do a sort on the serial number column (say column B) (carrying the others along with necessary).
Then in a new column put a 1 in the first cell (say C1) and in C2 put

=if(b2<>b1,c1+1,c1)

and copy that down the rest of column C

5

Found this article, which suggests this formula (replace C3:C25 with your range):

=SUM(IF(FREQUENCY(IF(LEN(C3:C25)>0,MATCH(C3:C25,C3:C25,0),“”), IF(LEN(C3:C25)>0,MATCH(C3:C25,C3:C25,0),“”))>0,1))

I haven’t tried it, so use it at your own risk. Don’t blame me when your computer explodes.

6

If you want a down and dirty method then do a pivot table and then use a count function to count the return.

example: (you need to have a header in your column so for this example I would name it “stuff”)

highlight the column, and under “Data” (from tool bar on top of sheet) choose Pivot Table Report. Hit next twice until you get to step 3 of the PivotTable Wizzard. You will see a box titled “stuff” to the right. Drag it into the area called Data. Double click on the newly dragged item and choose “count” from the choices listed under “Summarize by”. Then go back to the original box to the right titled “stuff” and drag it to the area to the left called Row. Click on finish and you will get a count of each unit item. This should create a new sheet of 2 columns that looks like this:
Count of data2
data2_______Total
123456______1
123abc______3
abc123______1
abcdef______1
Grand Total 6

From here you use the count function as follows: =count(b3:b6)

This may sound complicated if you have never used a Pivot Table but it’s really easy.

7

If you want to do this by formula only then you can sort the list and use a formula to the right such as: =if(a2=a1,"",1) and then use the count function. The list has to be sorted first and what this does is look at the number to the left, compares it to the number above and if they are the same return nothing (anything between quotes is considered text and will return a blank that is not a numerical number).

8

So as not to confuse you, I named my data “data” and not “stuff” as was the example posted. :smack: I changed it to stuff in my description to avoid confusing you.:smack::smack:

So name your column whatever you want and that will show up on the right in step 3 of the Pivot Wizard. If you named it “stuff” like I suggested the Pivot Table results would look like this:

Count of stuff
stuff______Total
123456____1
123abc____3
abc123____1
abcdef_____1
Grand Total__6

9

Assuming your values are in column A with a header label in A1, this formula will put a 1 in a cell in row 2 if the value in A2 has not appeared in the in column A already.

=IF(ISNA(VLOOKUP(A2,$A$1:A1,1,FALSE)),1,0)

Note that when you copy it, the lookup range always goes from the header cell to the cell above the one being evaluated. At this point just sum the column with this formula.

If you don’t need the number of values always available, just do an advanced filter for unique values and count the results.