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

*Article last updated on April 24, 2017*

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

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. What's on this […]

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 […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Count cells between a given value

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 […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Count unique distinct values in two columns

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]

Count specific text string in a cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.