## Count the number of cells within a range that match multiple comma separated values

I received an email from one of my seven blog readers.

*In Excel, I have a column, say A, with some cells blank and some cells with text.*

*In another column, say B, each *cell* contains many words, separated by a comma.*

*For every cell in B, I need to check to see how many of the words from column A are in the cell and output the total count of matches.*

I built a user defined function for this, if you have a regular formula you think can solve this, please share. This animated picture explains it all.

The formula in cell C2 is a user defined function. You build a udf just like a macro using the visual basic of application language (vba).

The first argument (B2) in this custom made function is a cell reference to comma-separated values, in a single cell. The second argument ($A$2:$A$20) is a reference to cell range that you want to count, make sure it is a single column cell reference.

### Build a user defined function

To build a user defined function, follow these steps:

- Press Alt + F11 to open the visual basic editor
- Click "Insert" on the menu
- Click "Module"

- Copy the code below and paste it to the code module.

### User defined function vba code

Function CountWords(a As String, b As Range) Dim Words() As String Dim Value As Variant, cell As Variant Dim c As Single Words = Split(a, ",") For Each Value In Words For Each cell In b If UCase(WorksheetFunction.Trim(cell)) = UCase(WorksheetFunction.Trim(Value)) Then c = c + 1 Next cell Next Value CountWords = c End Function

### Explaining the user defined function

**Function name and arguments**

A user defined function procedure always start with "Function" and then a name. This udf has two arguments, a and b. Variable a is a string and b is a range.

Function CountWords(a As String, b As Range)

**Declaring variables**

Dim Words() As String Dim Value As Variant, cell As Variant Dim c As Single

Words() is a dynamic *string* array. Value and cell are *variants*. c is a *single* data type. Read more about Defining data types.

**Split function**

Words = Split(a, ",")

The Split function accepts a text string and returns a zero-based, one-dimensional array containing all sub strings. Split allows you also to specify a delimiting character, default is the space character.

**For ... Next statement**

For Each Value In Words ... Next Value

Repeats a group of statements a specified number of times. In this case as many times as there are values in the array Words.

**If function**

If UCase(WorksheetFunction.Trim(cell)) = UCase(WorksheetFunction.Trim(Value)) Then c = c + 1

The Ucase function converts a string to uppercase letters. The WorksheetFunction.Trim method removes all spaces from text except for single spaces between words.

The If function compares cell and Value and if they match 1 is added to c.

**The udf returns...**

CountWords = c

The udf returns the value in c.

**End a udf**

End Function

A function procedure ends with the "End function" statement.

### Recommended reading

- Excel udf: Lookup and return multiple values concatenated into one cell
- Filter unique words from a range in excel (udf)
- Excel udf: Filter unique distinct values (case sensitive)
- Excel udf: Filter unique distinct records (case sensitive)
- User defined function to split words in a cell range into a cell each in excel
- Excel udf: Filter common values between two cell ranges in excel
- Excel udf: Filter values existing only in one out of two ranges

### Download excel * .xlsm file

Count the number of cells within a range that meet multiple comma separated values.xlsm

### Category: Count values

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

Count unique distinct values in a column

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: First, let me explain […]Comments(26) Filed in category: Count unique distinct values, Count values, Excel

Count number of times a string exist in multiple cells using excel formula

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]Comments(19) Filed in category: Count text values, Count values, Excel

Comments(16) Filed in category: Count values, Excel, Frequency

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

Count unique distinct values in two columns in excel

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]Comments(14) Filed in category: Count unique distinct values, Count values, Excel

Count records between two dates with multiple parameters in excel

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]Comments(14) Filed in category: Count values, Dates, Excel

Count text that occurs multiple times in excel cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]Comments(9) Filed in category: Count text values, Excel

Count occurences of a specific text string in a column in excel

Question: How do I count the number of times a text string exists in a column? Answer: Array formula in […]Comments(9) Filed in category: Count text values, Count values, Excel

### 7 Responses to “Count the number of cells within a range that match multiple comma separated values”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

As long as the Values list in Column A is in alphabetical order, this formula appears to work...

=SUMPRODUCT(0+(LOOKUP(TRIM(MID(SUBSTITUTE(","&B2,",",REPT(" ",999)),ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))*999,999)),A$2:A$20)=TRIM(MID(SUBSTITUTE(","&B2,",",REPT(" ",999)),ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))*999,999))))

Rick,

Impressive formula! I had to remove a leading blank in cell A8 to make it count correctly. Perhaps wordpress filtered out some html characters from your formula again?

I don´t know why I made the values in col A in alphabetic order, it was not my intention.

That looks really good.

Is there a way to extract the values (rather than count them) and place the results in column C?

For example:

Cell B3 ("jj,oo,pp").

In column C3, it returns "2" as 2 of the values in cell B3 were found in column A

However, I am after which 2 values were found in column A

i.e. I am after a formula that returns "jj,pp") in cell c3 rather than "2"

is this possible?

thanks

It is LONG, but I can reduce the CountWords UDF to a single line of code (no loops needed)...

Rick Rothstein (MVP - Excel),

Your function returns 2 for values in cell B2? See above. I am not sure why?

That is because your word list is not "pure"... the value in cell A8 has a leading space in front of it... remove it and the UDF will return 3 as expected. In passing, I would not expect a look-up list to have either leading or trailing spaces... if they must be allowed, then I do not think I can modify my one-liner UDF to allow for them.

Thanks for explaining, your one-liner UDF is great.