Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Count Number Of Unique Values In A Single Cell Separated By Commas

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi, probably a very easy one here, im looking to count the unique number of values within a single cell string which are separated by commas


Cell A1 contains: 1,2,3A33,444,2,1,16

The resulting formula would give a count of 5 unique value within cell A1 as values '2' and '1' are repeated twice within the cell and would therefore be excluded.

Any help would be most appreciated, thank you!

View Answers     

Similar Excel Tutorials

How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...
Require a Unique List of Numbers in a Range in Excel
I'll show you how to require a user to enter a unique number into a range of cells in Excel.  This feature will req ...
OFFSET Function in Excel
The OFFSET function in Excel returns a cell or range reference that is a specified number of rows and columns away ...
Dynamic Formulas that Update When you Add Data in Excel
I'll show you how to make formulas and functions that automatically update when more data is added to a range in E ...

Helpful Excel Macros

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

Similar Topics

Hi all,

Does anyone know a formula that will count the number of UNIQUE numbers in a single cell (but ignore duplicates).

All the values are separated by a "-" if that helps

The above will be 6 because "3" appears twice

The above will be 7 because they are all unique values

Thanks in advance

Is it possible to have a formula that can count the amount of values in a single cell?


Cell A2 contains "C1, C2, C3, D1, D4, D5"
is there a formula i can put in Cell B2 that will count how many values there are in A2?

so cell B2 would then say "6"

Every value is separated by a comma and a space exactly as above (if that helps)


Sorry if that's a bit long winded...

Here's how it breaks down.

If I have, in one cell (call it D1):


and in a lookup table on another sheet:
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...



Does that make sense or should I resubmit?

I've tried a few different ways but can't come up with anything...

There's more to the project, but this bit will help me get it off the ground and hopefully I'll be able to string the rest together.

Cheers guys! Great forum!

I have a single cell with 4 values in it all separated by commas and i would like to pull out each one separately. The number of characters in each value changes. Hopefully someone can help.




Does anyone know any easy formula to count the number of UNIQUE values in a range of cells?

Note that the values include both numbers and text. Ideally, the solution could be implemented in a formula contained within a single cell.

Here is an example of the data and solution


Solution = 4


This might not be possible with out a VB function, but I figured I would ask the experts.

Basic problem is that I have a column of data that can have a single identifier or more then one identifier in a cell with a separation of a coma. I need a way to count up all the unique items listed, but needs to be able to tell if the values in the cell are unique not the whole cell.



Count Result = 7 since there is no 2 or 4 in the list and 11 is counted as a whole number insted of 1 and 1.

Good morning everybody.

I have a range of cells containing names of scientists separated by a pipe delimiter.

For example
Propp; Michael B.|Propp; David L.
De Gaudenzi; Riccardo|Viola; Roberto|Elia; Carlo
Benedicto Ruiz; Francois-Xavier|Lopriore; Mario|Bella; Luigi

I needed to count the number of scientists in each cell, assuming they are separated by a pipe delimiter. So, the formula would need to return the value 2 for the first of my examples, and 3 for the second and the third.

It needs to therefore count the number of delimiters and return the value of that count + 1 - i.e. if there are two delimiters in the string, then there must be three items, and so on.

Have any ideas?

Thank you so much!

I have a sheet with many entries in each row. Some match others don't...i want to be able to highlight the populated cells in the row, and concatenate a string of unique values separated by commas and ready to paste into another workbook.

any takers?


I have a column of codes. The codes have a prefix, hyphen, followed by a number. There can be multiple codes, single codes or no codes in a cell. I need a formula that counts how many unique codes are the column. Example column below:

INA-721, INA-521, INB-222

INA-123, INA-234, ING-111

INA-721, INA-123

I need a formula that produces a count of unique codes per prefix in place of the the numbers below:
Ind. Codes [B}# of Unique Codes[/B]
INA codes = 5
INB codes = 1
IND codes = 2
ING codes = 1

I have tried counting the code prefixes but that doesn't provide me the unique total.
I have tried counting the unique cells on this column but this considers the cells with mulitple values as unique, not looking at the individual values within the cells.

Have numerous values in Col A. Col E extracts a list of unique values from that column.
In Col C, the Col A value has had characters added to it.

Need a formula to count the number of unique values from Col C which contain the same prefix from Col A, and place the result in Col F.

A sample workdook is attached with the desired result shown and highlighted in yellow.

Many thanks for your thoughts on this.Excel Forum.xlsx



I am after something that seems simple, but I can't figure out a way to do it.

If I have a single cell containing a list of values seperated by a comma (ie a csv list in a cell), how do I count the number of values. I figure it must be by counting the commas somehow and then adding one as the last doesn't have it, but there is no way to tell how many there will be.

My cell contents look like this
18911, 18801, 18825, 18482, 18941

so I want to have another cell return the number 5.

Does anyone have any ideas?


I can get a count for the number of cells within a column or row, but how can i find out how many unique values there are?


While a count will give me 8 values, I only want to return 6 for the unique references

Hi, I am wondering if it is possible to count the number of unique values in two columns greater than some other value. To clarify, if I have two columns (A and B) with the values listed below.


3 6
2 3
4 2
6 8
5 10

I want to count how many unique values between the two columns are less than 5. In this case, there should be 3 (2,3 and 4 from column A as well as 2 and 3 from B fulfill this condition). Is there any way to do this efficiently without using VBA? I have searched this forum and a few others and have seen SUM(IF(FREQUENCY(...)) being used in single column counts, but I don't know how to adapt this to multiple columns of data. Thanks.

I have been using this function to count the number of unique text values in a data set:

It works great if I want to count number of unique text values overall. However, I want to count the occurences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).

Any ideas?


I need help performing a lookup function with inputs in one cell separated by commas.

I have a single cell with a given number of initials (my lookup values):


I have a table with a cost assigned to each of these initials (my lookup array):

RY 200
AH 200
NR 400

I would like to perform a vlookup off the cell containing the comma separated values to generate an output such as 200, 200, 400.

I would also like the function to be flexible enough to provide outputs based on any given number of inputs in the cell such as
RY, AH, NR, WG or

Thank you so much for anyone's help on this!


I've tried various formulas to count the number of unique values, but I haven't found one that accounted for my second criteria. What I'm trying to do is to count the number of unique Type #'s but only for the ones that will be included. I hope my request makes sense, but if not just let me know. Thanks.

I have an excel sheet that has multiple rows for one PO number. I've created a row at the top to calculate all of the unique PO numbers in the sheet. However, I want to create another column that will give each unique PO number it's own ID number starting at 1. For instance:

Column B (PO Number)
4 - Counts unique values in B

Then in Column C I'd want
1 (unique number for 219)
1 (unique number for 219)
1 (unique number for 219)
2 (unique number for 220)
2 (unique number for 220)
3 (unique number for 221)
4 (unique number for 222)
4 (unique number for 222)

Any ideas? If I'm not making sense just say so and I'll try to clarify.


I have 50 questions that have several individuals accountable. I am trying to have data auto-fill from the Deficiencies Worksheet into the Accountability Worksheet. Currently I know how to count the number of cells containing the information I want based on criteria utilizing the COUNTIF formula in Column B (Total Number) in the Accountability Worksheet).

What I am struggling with in the Accountability Worksheet:

1. Automate Column A (Team Member, highlighted in GREEN) so as to auto-fill the names alphabetically based on the Deficiencies Worksheet column E (Accountable, highlighted in BLUE).

2. Automate Column C (Deficiency Number, highlighted in YELLOW) to provide the information that is currently there now (Meaning: Taking the data from Deficiencies Worksheet I want the Value of Cells in Column A (highlighted in PURPLE) separated by commas, if applicable, associated with the Name of the Accountable.
NOTE: I figured out how to do this for a single cell, but don't know how to return multiple cell values or text and then separate by commas plus the added complexity of non-contiguous cells.

3. Automate Column D (Deficiency Approved, highlighted in RED) to provide the Question # approved based on Column Q (Response Approved, highlighted in GREY) in the Deficiencies Worksheet. NOTE: I know how to do this for a single cell, just not multiple non-contiguous cells so that I can return each Question # separated by a comma.

4. Finally is there a way to count the number of Deficiencies Approved in Column D or would that have to be based on the Deficiencies Worksheet Column Q again?

I have tried the LOOKUP formulas, but they only work utilizing data in the first column, row or ascending value and none of those apply here...additionally the cells are (as stated above) non-contiguous.

This is my first post so I hope I perform this attachment correctly.


What I have a cell with values separated by , (commas).
Ex: Apple, Orange, Pea

The number of values will vary per worksheet and what I would like to do is separate out those values into individual cells. I know i know - text to columns... :o Slow down - there is data in the adjacent cell that I don't want to overwrite.
What I would like to do is count the occurances of the , (comma separating the values) and insert that number+1 columns to the right of it.

If that wasn't enough - I would then like to use a vlookup to look up those indiviual results and return another value (from a different worksheet).

So I have:
Cell A1=Banana, Apple, Orange;
Cell B1=Data I Need

What I want:
Cell A1=vlookup(banana);
Cell B2=vlookup(apple);
Cell C3=vlookup(orange);
Cell D4=Data I need (from cell b2...ZZ2)

Any suggestions on where to begin - is there a count feature that will count indiviudal occurances of the comma in the cell that I could then use to get the number of columns to insert?

Thanks for any and all help

Say in Cell A1, I have numbers separated by commas, like this:

123, 435, 456, and so on, many, like over 100 like this in a single cell,

I would like to paste those values so that I have 100 values spread over single cells, they would go like this:

B1: 123
B2: 435
B3: 456

Has anyone figured this out ?

Manual way and VBA examples would be nice, thanks for your time!

Dear Most Amazing Excelrs In The World,

I am trying to count the unique values that are greater than 30 in the range A1:A5. The values a


The answer should be 2 because 45 and 90 would be counted.

I have successfully created a few solutions with a Pivot Table and Advanced Fliter, but when I try to do one Array formula in one cell, I have no luck.

Any ideas about how to count the unique values that are greater than 30 in the range A1:A5 with an array formula?

Need a formula in Col AF to count the number of unique digits, without counting duplicate digits. The 2-digit values are to be treated as 2 separate values. If the value has a leading zero, (example AA1= 06, don't count zero. All 2-digit values 10,20,30, count the zero).
The unique digits below are 1,2,3,6,8 for a total of 5.
06 18 23 28 32 5

UNIQUE VALUES Z AA AB AC AD AE AF 1 06/11/10 06 18 23 28 32 5 2 06/10/10 01 14 20 22 35 6 3 06/09/10 02 09 10 17 20 6 4 06/08/10 11 15 24 27 34 5 06/07/10 01 08 18 25 27 Excel 2007


I am trying to create an array formula:

I have account numbers in column A and values in column B (The 1 could represent any number)

102 0
102 0
103 1
103 1
104 0
104 0
104 1

I would like to count the number of Unique account numbers in A if the value of B is 1

ie the correct answer should be 2

as I am using 2 account numbers

I have 2 formulas to count the number of unique values


and an array formula


both return the number of unique value in column A which in this case is 3

but am having problems trying to link column B to column A
ie when column B shows a zero in a cell do not include its corresponding cell in Column A in the unique count.

Can anyone help me

Kind Regards


I know that to join the contents of several cells into a single one (concatenate is the technical word), all it takes is the function & and that to have values separated by a comma and a space all you have to do is:
a1&", "&a2&", "&a3&", "&a4

My problem is that sometimes some of these cells are empty so I end up with some values separated by commas, empty spaces and more commas without more values in between.

What would be the way to concatenate only the non-empty cells?



Please advise on how to count unique date values (e.g., 2/4/07, 2/4/07, 2/5/07, 2/6/07 = 3 unique values).

I'm working on a spreadsheet which has a date column with duplicate dates. I need to count the number of unique dates.

Your help is greatly appreciated!!!