Count cells containing text from list

1. Count cells containing text from list

The array formula in cell F3 counts cells in column B that contains at least one of the values in D3:D5. Each cell is only counted once.

To enter the array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining the formula

The TRANSPOSE function changes the values in D3:D5 from being vertically arranged to being horizontally arranged.

TRANSPOSE(D3:D5)

Note the semicolon and comma characters that separate the values below.

{"B";"L";"O"} => {"B","L","O"}

The SEARCH function requires the values to be arranged in one column in the first argument and in one row in the second argument or vice versa.

That is why the TRANSPOSE function is needed, you could, of course, enter the values horizontally on the worksheet to avoid the TRANSPOSE function.

SEARCH(TRANSPOSE(D3:D5), B3:B14) returns the following array, displayed in the picture below.

Example, B is found in character position 4 in text string N, B, L, F, I. Note that the SEARCH function returns a #VALUE error if nothing is found.

The ISNUMBER function returns TRUE or FALSE determined by a value is a number or not, it happily ignores errors.

ISNUMBER(SEARCH(TRANSPOSE(D3:D5), B3:B14)) returns the following array.

The MMULT function sums the values row by row and returns an array shown in the picture below.

MMULT(ISNUMBER(SEARCH(TRANSPOSE(D3:D5), B3:B14))*1, ROW(D3:D5)^0)

To be able to do that we must use this array as the second argument: {1;1;1} It is determined by the number of cells in the list, in this case, three. They must be 1 and arranged vertically.

That is why I built this formula that builds the array automatically: ROW(D3:D5)^0

The MMULT function can't work with boolean values so I multiply them all by 1 to convert them into 0 (zeros) or 1.

The next thing is to check if the values in the array are larger than 0 (zero).

MMULT(ISNUMBER(SEARCH(TRANSPOSE(D3:D5), B3:B14))*1, ROW(D3:D5)^0)>0

Lastly, the SUM function adds the numbers and returns a total in cell F3.

Get Excel *.xlsx file

Count cells containing text from list.xlsx

Check out this article if you want to count all text strings found in a cell range, in other words, cells might be counted twice or more.

Recommended articles

2. Count entries based on date and time

Answer:

Formula in cell F6:

Explaining formula in cell F6

The COUNTIFS function was introduced in Excel 2007 and it works like the COUNTIF function except you may use multiple conditions at the same time.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Pair	criteria_range	criteria	Text
1	B3:B17	"<="&F3	Are dates in B3:B17 smaller than end date in cell F3?
2	B3:B17	">="&F2	Are dates in B3:B17 larger than start date in cell F2?
3	C3:C17	F4	Are Items in c3:C17 equal to cell F4?

The ampersand character concatenates the logical operators <> and = to each cell or cell range before the COUNTIFS function evaluates the argument. If all conditions return TRUE then the record is counted as 1.

Step 1 - Criteria pair 1

The following image shows in column D date and time entries smaller than or equal to condition in cell F3, TRUE - Smaller, FALSE - larger.

Step 2 - Criteria pair 2

This image shows in column E date and time entries larger than or equal to condition in cell F2, TRUE - Smaller, FALSE - larger.

Step 3 - Criteria pair 3

This picture displays in column F items equal to condition in cell F4.

Step 4 - All conditions applied

This image shows which entries meet all conditions. If all conditions evaluate to TRUE then that specific record is counted, the formula returns 2 in cell F6 because two records meet all conditions.

Excel 2003 (and earlier versions) formula in cell F6:

3. Count cells with text

Table of Contents

1. Count cells with text
2. Count cells with text excluding cells containing a space character
3. Count text values returned from an Excel function
4. Count text values excluding numbers stored as text
5. Get Excel *.xlsx file

3.1. Count cells with text

The following formula in cell D3 counts cells with values stored as text.

In other words, cells containing nothing, errors, boolean values, and numbers are not counted.

Numbers stored as text are counted, as well as cells containing a space a character or more.

3.1.1 Explaining formula

Step 1  - Identify values stored as text

The ISTEXT function returns TRUE or FALSE depending on if a cell has a value stored as text.

ISTEXT(B3:B14)

becomes

ISTEXT({"ZF"; 0; 5; "T5"; 0; #N/A; "SK"; "JK"; 0; "DP"; 3; #DIV/0!})

and returns

{TRUE; FALSE; FALSE  ... }.

Step 2 - Convert boolean values to numbers

To count boolean values (TRUE, FALSE) we need to convert them into numbers. TRUE = 1 and FALSE = 0 (zero).

ISTEXT(B3:B14)*1

becomes

{TRUE; FALSE; FALSE  ... }*1

and returns

{1; 0; 0; ... }.

Step 3 - Count numbers

The SUMPRODUCT function has a great advantage over the SUM function, in most cases, you don't need to enter the formula as an array formula if you are working with arrays.

SUMPRODUCT(ISTEXT(B3:B14)*1)

becomes

SUMPRODUCT({1;0;0;1;0;0;1;1;0;1;0;0})

and returns 5 in cell D3.

1+0+0+1+0+0+1+1+0+1+0+0 = 5.

Back to top

3.2. Count cells with text excluding cells containing a space character

Cell B4 contains a space character, the array formula below does not count cells containing a space character.

B3:B14<>" "  makes sure that cells containing a space character are not counted.

Back to top

3.2.1 How to enter an array formula

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Back to top

3.2.2 Explaining formula

Step 1 - Check if a value is text

The ISTEXT function returns TRUE if argument is text.

Function syntax: ISTEXT(value)

ISTEXT(B3:B14)

becomes

ISTEXT({"ZF"; " "; 5; "T5"; 0; #N/A; "SK"; "JK"; 0; "DP"; 3; #DIV/0!})

and returns

{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE}.

Step 2 - Check if a value is equal to a space character

The less than and greater than characters let you evaluate "not equal to" between two or more values.

B3:B14<>" "

becomes

{"ZF"; " "; 5; "T5"; 0; #N/A; "SK"; "JK"; 0; "DP"; 3; #DIV/0!}<>" "

and returns

{TRUE; FALSE; TRUE; TRUE; TRUE; #N/A; TRUE; TRUE; TRUE; TRUE; TRUE; #DIV/0!}

Step 3 - Replace text values with the result of a logical test

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(ISTEXT(B3:B14),(B3:B14<>" "),0)

becomes

IF({TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE},{TRUE; FALSE; TRUE; TRUE; TRUE; #N/A; TRUE; TRUE; TRUE; TRUE; TRUE; #DIV/0!},0)

and returns

{TRUE; FALSE; 0; TRUE; 0; 0; TRUE; TRUE; 0; TRUE; 0; 0}.

Step 4 - Multiply by 1 to convert boolean values to their numerical equivalents

The asterisk character lets you multiply numbers in an Excel formula.

IF(ISTEXT(B3:B14),(B3:B14<>" "),0)*1

becomes

{TRUE; FALSE; 0; TRUE; 0; 0; TRUE; TRUE; 0; TRUE; 0; 0}*1

and returns

{1; 0; 0; 1; 0; 0; 1; 1; 0; 1; 0; 0}.

Step 5 - Add numbers and return a total

The SUM function allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

Function syntax: SUM(number1, [number2], ...)

SUM(IF(ISTEXT(B3:B14),(B3:B14<>" "),0)*1)

becomes

SUM({1; 0; 0; 1; 0; 0; 1; 1; 0; 1; 0; 0})

and returns 5.

Back to top

3.3. Count text values returned from an Excel function

The formula below lets you count text values returned from an Excel function.

The ISTEXT function handles empty values from an Excel function as a text value. To avoid counting those non-values I simply use the smaller than and larger than signs <> meaning not equal to.

3.1 Explaining formula

Step 1 - Check if value is text

The ISTEXT function returns TRUE if argument is text.

Function syntax: ISTEXT(value)

ISTEXT(C3:C14)

becomes

ISTEXT({22; "A"; "A"; "A"; ""; "A"; "A"; 22; ""; 22; ""; "A"})

and returns

{FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE}.

Step 2 - Check if a value is not empty

The less than and greater than characters lets you evaluate if a value is "not equal to" another value, the result is a boolean value TRUE or FALSE.

C3:C14<>""

becomes

{22; "A"; "A"; "A"; ""; "A"; "A"; 22; ""; 22; ""; "A"}<>""

and returns

{TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}.

Step 3 - Multiply arrays (AND logic)

ISTEXT(C3:C14)*(C3:C14<>"")

becomes

{FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}

and returns

{0; 1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1}.

Step 4 - Add values and return a total

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

Function syntax: SUMPRODUCT(array1, [array2], ...)

SUMPRODUCT(ISTEXT(C3:C14)*(C3:C14<>""))

becomes

SUMPRODUCT({0; 1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1})

and returns 6.

Back to top

3.4. Count text values excluding numbers stored as text

This formula counts cells containing text values excluding numbers stored as text from the count.

Cell B5 contains a number stored as text, to exclude that number from the count use the following formula:

3.4.1 Explaining formula

Step 1 - Check if a value is text

The ISTEXT function returns TRUE if argument is text.

Function syntax: ISTEXT(value)

ISTEXT(B3:B14)

becomes

ISTEXT({"ZF";0;"5";"T5";0;#N/A;"SK";"JK";0;"DP";3;#DIV/0!})

and returns

{TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE}.

Step 2 - Check if a value is a number

The asterisk character lets you multiply numbers in an Excel formula. It returns a product if both values are numbers and an error if not.

It also converts numbers stored as text like "5" to 5.

B3:B14*1

becomes

{"ZF";0;"5";"T5";0;#N/A;"SK";"JK";0;"DP";3;#DIV/0!}*1

and returns

{#VALUE!; 0; 5; #VALUE!; 0; #N/A; #VALUE!; #VALUE!; 0; #VALUE!; 3; #DIV/0!}.

Step 3 - Check if a value is a number

The ISNUMBER function checks if a value is a number, returns TRUE or FALSE.

Function syntax: ISNUMBER(value)

ISNUMBER(B3:B14*1)

becomes

ISNUMBER({#VALUE!; 0; 5; #VALUE!; 0; #N/A; #VALUE!; #VALUE!; 0; #VALUE!; 3; #DIV/0!})

and returns

{FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}.

Step 4 - Perform boolean opposite

The NOT function returns the boolean opposite to the given argument.

Function syntax: NOT(logical)

NOT(ISNUMBER(B3:B14*1))

becomes

NOT({FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE})

and returns

{TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}.

Step 5 - Multiply arrays

The asterisk character lets you also multiply boolean values, this performs AND logic on two arrays. AND logic returns TRUE if both values are TRUE.

TRUE * TRUE = TRUE (1)
TRUE * FALSE = FALSE (0)
FALSE * FALSE = FALSE (0)

The multiplication also converts boolean values to their numerical equivalents.

TRUE - 1
FALSE - 0 (zero)

ISTEXT(B3:B14)*NOT(ISNUMBER(B3:B14*1))

becomes

{TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE} * {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}

and returns

{1; 0; 0; 1; 0; 0; 1; 1; 0; 1; 0; 0}

Step 6 - Add values and return a total

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

Function syntax: SUMPRODUCT(array1, [array2], ...)

SUMPRODUCT(ISTEXT(B3:B14)*NOT(ISNUMBER(B3:B14*1)))

becomes

SUMPRODUCT({1; 0; 0; 1; 0; 0; 1; 1; 0; 1; 0; 0})

and returns

5. 1 + 1 + 1 + 1 + 1 = 5

Back to top

3.5. Get Excel *.xlsx file

Count cells with text.xlsx

Back to top

4. Count specific multiple text strings in a given cell range

This section demonstrates an array formula that counts how many times multiple text strings exist in a cell range.  The formula takes into account if a text string exists multiple times in a single cell, see picture above. Text strings "Car" and "bike" exist 5 times in cell range B3:B6.

Table of contents

1. Count specific multiple text strings in a given cell range - case sensitive
2. Count specific multiple text strings in a given cell range - case insensitive

4.1. Count specific multiple text strings in a given cell range - case sensitive

It is easy to add more text strings, and adjust cell range E2:F2 in the formula. The following formulas are case-sensitive, however, I will be demonstrating case-insensitive formulas later in this section.

Array formula in cell E4:

or use this slightly larger regular formula:

How to enter an array formula

1.  Select cell E4.
2. Press with left mouse button on in the formula bar.
   
3. Copy above array formula and paste to the formula bar.
4. Press and hold CTRL + SHIFT simultaneously.
5. Press Enter once.
6. Release all keys.

The formula bar displays curly brackets:

Don't enter the curly brackets yourself, they appear automatically.

Explaining array formula in cell D2

Excel has a built-in tool named "Evaluate Formula" tool, it allows you to examine formulas and the calculations they make step by step.

Go to tab "Formulas" on the ribbon, press with left mouse button on "Evaluate Formula" button and a dialog box appears.

Press with left mouse button on the "Evaluate" button on the dialog box to move to the next calculation step, this way you can see all calculation steps a formula does making it easier for you to understand and troubleshoot a formula.

The most important thing to understand in this formula is how arrays can be used.

Step 1 - Count characters in each cell

The LEN function returns a number representing the number of characters in a cell. In this case, the LEN function works with a cell range returning an array of numbers.

Len(B3:B6)

becomes

Len({"Car train"; "airplane bike"; "boat Car"; "rocket Car bus Car"})

and returns {9; 13; 8; 18}.

Step 2 - Replace existing text with the new text string

The SUBSTITUTE function replaces a specific text string in a value, it is case sensitive. The SUBSTITUTE function replaces multiple strings with nothing in this formula.

SUBSTITUTE(text, old_text, new_text, [instance_num])

The old_text argument allows you to use a cell range D1:E1 which is handy in this scenario, note that it returns an array containing eight values.

The first row in the array contains values without the first text string (D1) and the second row contains values without the second text string (E1). This is not a problem as you will see when we go through the remaining steps.

SUBSTITUTE(B3:B6,D1:E1,"")

becomes

SUBSTITUTE({"Car train"; "airplane bike"; "boat Car"; "rocket Car bus Car"},{"Car", "bike"},"")

and returns the following array, see E3:F6 in the image below.

Values in an array are separated by a comma or a semicolon, commas are used between columns and semicolons between rows.

Note, your Excel version may use other characters than commas and semicolons based on the regional settings on your computer.

Step 3 - Count characters in each cell

In this step the LEN function calculates the length of each value in the array after the SUBSTITUTE function has replaced the given text strings.

LEN(SUBSTITUTE(B3:B6, E2:F2, ""))

returns the array displayed in E3:F6 in the image below.

Step 4 - Subtract original character length with substituted values

This step calculates the difference between the number of characters of each value in the array. But the array sizes do not match? The number of horizontal values matches which makes it possible to calculate this arithmetic operation.

LEN(B3:B6)-LEN(SUBSTITUTE(B3:B6,E2:F2,""))

returns the array displayed in E3:F6 in the image below.

Step 5 - Divide with length of each search string

We now know where the given text strings are in the array and also how many they are in each value based on character length. If we divide the length of each value in the array with the numbers of characters in each given text string we can calculate how many times they exist.

(LEN(B3:B6)-LEN(SUBSTITUTE(B3:B6,E2:F2,"")))/LEN(E2:F2)

returns the array displayed in E3:F6 in the image below.

Step 6 - Sum all values

The SUM function adds all numbers in the array.

SUM((LEN(B3:B6)-LEN(SUBSTITUTE(B3:B6,E2:F2,"")))/LEN(E2:F2)

becomes

SUM({1,0;0,1;1,0;2,0})

and returns 5 in cell E4.

Back to top

4.2. Count specific multiple text strings in a given cell range - case insensitive

The following formulas are not case sensitive in terms of search values, see image above.

Array formula in cell E4:

or use this slightly larger regular formula:

The difference between these formulas and the case-sensitive formulas is the UPPER function. It converts letters to upper case letters.

Back to top

5. Count identical values if they are on the same row

This article describes a formula that counts values in two columns if they are duplicates on the same row.

What's on this section

1. Count identical values if they are on the same row (Array formula)
2. Count identical values if they are on the same row (Regular formula)
3. Count identical values on the same row comparing values in n columns
4. Get Excel file

5.1. Count identical values if they are on the same row (Array formula)

Kidd asks:

Array formula in cell E2:

5.1.1 How to enter an array formula

1. Select cell E2
2. Paste the formula in formula bar
3. Press and hold CTRL + SHIFT simultaneously
4. Press Enter
5. Release all keys

Your formula now begins and ends with a curly bracket, if you did it right.

Like this {=SUM((A2:A6=B2:B6)*1)}

Don't enter the curly brackets yourself, they appear automatically.

Back to top

5.1.2 Explaining formula

Step 1 - Compare values in column A with column B

The equal sign lets you compare value to value, the result is boolean value TRUE if they match (not case-sensitive) and FALSE if they don't.

A2:A6=B2:B6

becomes

{12; 8; 22; 7; 14}={15; 8; 19; 22; 14}

and returns

{FALSE; TRUE; FALSE; FALSE; TRUE}

Step 2 - Multiply boolean values with 1

To be able to sum the values in this array {FALSE; TRUE;FALSE;FALSE;TRUE} we need to convert the boolean values to their numerical equivalents, FALSE = 0 (zero) and TRUE = 1.

(A2:A6=B2:B6)*1

becomes

({FALSE;TRUE;FALSE;FALSE;TRUE})*1

and returns

{0; 1; 0; 0; 1}

Step 3 - Sum values in array

The SUM function adds all numbers in the array and returns a total.

SUM((A2:A6=B2:B6)*1)

becomes

SUM({0; 1; 0; 0; 1})

and returns 2.

Back to top

5.1.3 Trim space characters

This formula also works with text values, to remove blanks before and after use TRIM function.

Back to top

5.2. Count identical values if they are on the same row (Regular formula)

Back to top

5.3. Count identical values on the same row comparing values in n columns

The formula in cell F3 counts the number of rows that contain the same value. In the example shown in the image above row 4, 7, and 9 contain the same value and the formula returns 3.

Note that this formula works with any cell range size, it does not need to be exactly three columns for this formula to work. However, you need to adjust the cell references accordingly in order to get a correct result.

Excel 365 dynamic array formula:

Here is how it works:

1. B3:D10=B3:B10: Compare each value on the same row to the first value on the same row. The result is a logic (boolean) array containing TRUE or FALSE.
2. AND(a)*1: Perform AND logic to each value on the same row, the result is TRUE or FALSE. Multiply the logical value by 1 to convert the boolean value to it's numerical equivalent.
3. BYROW(B3:D10=B3:B10,LAMBDA(a,AND(a)*1)): Perform the calculation per row. This returns an array containing 0 (zero) or 1.
4. SUM(BYROW(B3:D10=B3:B10,LAMBDA(a,AND(a)*1))): Add the numbers.

Formula for earlier Excel versions:

Back to top

5.3.1 Explaining formula

Step 1 - Compare values across columns

The equal sign lets you check if cell values match, the equal sign is a logical operator and returns a boolean (logical) value. TRUE if they match and FALSE if not.

B3:B10=B3:D10

becomes

{12; 8; 22; 7; 14; 5; 7; 11}={12,11,15; 8,8,8; 22,19,22; 7,8,22; 14,14,14; 5,2,6; 7,7,7; 11,18,12}

and returns

{TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, FALSE}

Step 2 - Convert boolean values

The MMULT function can't handle boolean values, we need to convert TRUE and FALSE to their numerical equivalents. TRUE - 1 and FALSE 0 (zero).

(B3:B10=B3:D10)*1

becomes

{TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, FALSE}*1

and returns

{1, 0, 0;1, 1, 1;1, 0, 1;1, 0, 0;1, 1, 1;1, 0, 0;1, 1, 1;1, 0, 0}

Step 3 - Calculate column numbers

The COLUMN function calculates the column numbers based on a cell reference.

COLUMN(B3:D10)

returns {2, 3, 4}.

Column B is 2, C is 3 and D is column number 4.

Step 4 - Convert all numbers to number 1

This step converts all column numbers to number 1. This is done by taking each number in the array raised to the 0 (zero) power.

COLUMN(B3:D10)^0

becomes

{2, 3, 4}^0

and returns {1, 1, 1}.

Step 5 - Convert a horizontal range to a vertical range

The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa.

TRANSPOSE(COLUMN(B3:D10)^0)

becomes

TRANSPOSE({1, 1, 1})

and returns {1; 1; 1}.

The colon and semicolon tell you if an array is arranged vertically or horizontally. This is determined by your computer's regional settings.

Step 6 - Calculate the number of matches per row

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MMULT(array1, array2)

MMULT((B3:B10=B3:D10)*1,TRANSPOSE(COLUMN(B3:D10)^0))

becomes

MMULT({1, 0, 0;1, 1, 1;1, 0, 1;1, 0, 0;1, 1, 1;1, 0, 0;1, 1, 1;1, 0, 0}, {1; 1; 1})

and returns {1; 3; 2; 1; 3; 1; 3; 1}.

Step 7 - Check if the number of matches is equal to the number of columns

MMULT((B3:B10=B3:D10)*1,TRANSPOSE(COLUMN(B3:D10)^0))=COLUMNS(B3:D10)

becomes

{1; 3; 2; 1; 3; 1; 3; 1}=3

and returns

{FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}.

Step 8 - Convert boolean values

The SUMPRODUCT function can't work with boolean values, we need to convert them to their numerical equivalents. TRUE - 1 and FALSE - 0 (zero).

(MMULT((B3:B10=B3:D10)*1,TRANSPOSE(COLUMN(B3:D10)^0))=COLUMNS(B3:D10))*1

becomes

{FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}*1

and returns {0; 1; 0; 0; 1; 0; 1; 0}.

Step 9 - Add numbers and return total

SUMPRODUCT((MMULT((B3:B10=B3:D10)*1,TRANSPOSE(COLUMN(B3:D10)^0))=COLUMNS(B3:D10))*1)

becomes

SUMPRODUCT({0; 1; 0; 0; 1; 0; 1; 0})

and returns 3.

Back to top

6. Count rows with data

The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data.

Formula in cell B17:

If your data set, for example, has 5 columns change:

• B3:D14 to your cell range
• the array from {1;1;1} to {1;1;1;1;1}, there must be as many 1's as there a columns in your data set.
• also change <3 to <5

Example, your cell range is A3:G14. The formula becomes:

Explaining formula in cell B17

Step 1 - Check if cell is empty

The equal sign allows you to compare each cell in B3:D14 with an empty value "".

B3:D14="" returns an array of boolean values indicating if a cell is empty or not. {FALSE, FALSE, FALSE; ... }

The picture above shows the array to the right and the corresponding values to the left.

Step 2 - Convert boolean values to numbers

To convert the boolean array to 1 and 0 (zero) I multiply with 1. The parentheses allow you to determine the order of operation.

I want to compare the values with "" before I mutlitply with 1.

(B3:D14="")*1 returns {0, 0, 0; ...)

The picture above shows the array to the right.

Step 3 - Add values row-wise

The MMULT function is great for adding values row by row, however, it can not handle boolean values. The function returns an array of values.

MMULT((B3:D14="")*1,{1;1;1})

There are two arguments in the MMULT function, array1 and array2.

The picture above shows you the result from the MMULT function in the blue rectangle.

To learn more about the MMULT function read this:

Recommended articles

Step 4 - Check if each value in the array is smaller than 3.

If there are three empty values in a row that row is empty. That is why I check if each row is less than 3 indicating that at least one cell is not empty.

MMULT((B3:D14="")*1,{1;1;1})<3 returns {TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}

The array is shown to the right in the picture above.

Step 5 - Count rows

To be able to sum the array of boolean values I have to multiply with 1 to convert them to 1 or 0 (zero). TRUE = 1 and FALSE = 0.

SUMPRODUCT((MMULT((A3:G14="")*1,{1;1;1;1;1;1;1})<7)*1)

becomes

SUMPRODUCT({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}*1)

becomes

SUMPRODUCT({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}*1)

becomes

SUMPRODUCT({1; 0; 1; 1; 0; 1; 1; 1; 0; 1; 1; 1}) and returns 9 in cell B17.

Why not use the SUM function? Then you would have to enter the formula as an array formula.

Get Excel *.xlsx file

Count rows with data.xlsx

7. Count complete rows

The following formula in cell B17 counts complete rows, in other words, all cells in a row must be non-empty.

See formula in picture above.

8. Count cells between two values

The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 and H is in B9, cells B4, B5, B6, B7 and B8 are in between.

Explaining formula in cell E16

Step 1 - Find value in cell D13 in cell range B2:B11

The MATCH function finds the relative position of a value in an array or cell range.

MATCH(D13, B2:B11, 0)

becomes

MATCH("B", {"A"; "B"; "V"; "X"; "C"; "T"; "N"; "H"; "A"; "C"}, 0)

and returns 2.

Step 2 - Find value in cell D14 in cell range B2:B11

MATCH(D14, B2:B11, 0)

becomes

MATCH("H", {"A"; "B"; "V"; "X"; "C"; "T"; "N"; "H"; "A"; "C"}, 0)

and returns 8.

Step 3 - Subtract positions

MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0)

becomes

2-8 equals -6.

Step 4 - Remove sign

We don't know where the values are in the cell range so it may happen that we get a negative number from time to time, this example is such occasion. The ABS function removes the sign from a number.

ABS(MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0))

becomes

ABS(-6)

and returns 6.

Step 5 - Subtract with 1

The calculation counts the last cell as well, we only need the cells in between.

ABS(MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0))-1

becomes

6-1

and returns 5 in cell E16.

Get Excel *.xlsx file

formula to count cells between two values.xlsx

9. Count cells based on a condition and month

Answer:

Formula in cell C1:

Copy cell C1 and paste down as far as needed.

Explaining formula in cell C1

Step 1 - Create arrays

The YEAR function returns the year from an Excel date.

=SUMPRODUCT(--(YEAR(A1)=YEAR($A$1:$A$30)), --(MONTH(A1)=MONTH($A$1:$A$30)), --(B1=$B$1:$B$30))

contains three criteria. The first criterion: --(YEAR(A1)=YEAR($A$1:$A$30))

becomes

--(2010=2010,  2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010)

becomes

--(TRUE,  TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE)

and creates this array: (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

The MONTH function returns a number from 1 to 12 representing the month. Jan = 1, Feb = 2 ... Dec = 12.

The second criterion: --(MONTH(A1)=MONTH($A$1:$A$30))

creates this array: (1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 0)

The third criterion: --(B1=$B$1:$B$30)

creates this array: (1, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1)

Step 2 - The product of three arrays

The SUMPRODUCT function multiplies the arrays (product) and then add the numbers in the array and returns a total (sum).

The entire formula now becomes

=SUMPRODUCT((1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), (1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 0), (1, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1))

and then

=SUMPRODUCT((1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)*(1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 0)*(1, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1))

becomes

Step 3 - Sum array

=SUMPRODUCT(1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0) equals 7.

Picture of formula calculations in cell C5

In order to show headers I had to adjust formula ranges:

10. Count cells between specified values

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values and unique numbers in column B.

If this is not what you are looking for then scroll down to see more examples.

Formula in cell E5:

Since there is only one instance of each search value in column B you only need a small formula to calculate the number of cells between the values.

Column A shows the cell count between the two values.

Explaining formula in cell E5

The formula finds the first value specified in cell E2 (45) in column B and returns the relative position of 45. It then continues with the second value given in cell E3.

The difference between the calculated relative positions is the number of cells between the values.

Step 1 - Find position of first value

The MATCH function returns the relative position of the value in cell E2 in cell range B2:B21.

MATCH(E2,$B$2:$B$21,0)

returns 6. Value 45 is the sixth value in the array.

Step 2 - Find position of second value

MATCH(E3,$B$2:$B$21,0)

returns 14. Value 115 is the 14th value in the array.

Step 3 - Subtract row numbers

MATCH(E2,$B$2:$B$21,0)-MATCH(E3,$B$2:$B$21,0)

becomes

6-14

and returns -8.

Step 4 - Remove sign

The ABS function converts negative numbers to positive numbers.

ABS(MATCH(E2,$B$2:$B$21,0)-MATCH(E3,$B$2:$B$21,0))-1

becomes

ABS(-8)-1

becomes

8-1 and returns 7 in cell E5.

10.1 Multiple values - two search values

The image above shows the second scenario, there are multiple instances of each search value in column B. This requires a somewhat more complicated formula to get the smallest number of cells between the two search values.

The order makes no difference, in other words, if search value 2 is found first makes no difference in this calculation.

Array formula in cell E5:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell E5

Step 1 - Calculate row numbers for first search value

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(B2:B25=E2, ROW(B2:B25), "")

returns {""; ""; ... ; 25}.

Step 2 - Calculate row numbers for second search value

The TRANSPOSE function rearranges values distributed vertically to horizontally.

TRANSPOSE(IF(B2:B25=E3, ROW(B2:B25), ""))

returns {"", "", ... , ""}.

Step 3 - Subtract first array with second array

IF(B2:B25=E2, ROW(B2:B25), "")-TRANSPOSE(IF(B2:B25=E3, ROW(B2:B25), ""))

returns an array too big to show here, however, an image would work if I replace all error values with a "-".

What does the image above tell us? It shows row numbers horizontally and vertically. The first search value (1) has its row numbers displayed vertically, there are four instances of the search value in column B, row 9, 14, 21 and 25.

The second search value har its row numbers displayed horizontally, it is found on row 11, 18 and 23. Example, the intersection of 9 and 11 shows -2. It means that 9-11 equals -2, the whole array shows the intersection between all instances of each search value.

This allows us to extract the smallest distance between two cells, or if you like the largest distance or any in between.

Step 4 - Convert negative values to positive values

The ABS function removes the minus sign from negative values.

ABS(IF(B2:B25=E2, ROW(B2:B25), "")-TRANSPOSE(IF(B2:B25=E3, ROW(B2:B25), "")))

Step 5 - Extract smallest number

The AGGREGATE function is better than the SMALL function, it also lets you ignore error values.

AGGREGATE(15, 6, ABS(IF(B2:B25=E2, ROW(B2:B25), "")-TRANSPOSE(IF(B2:B25=E3, ROW(B2:B25), ""))), 1)-1

becomes

2-1

and returns 1 in cell E5.

10.2 Multiple values - one search value

Answer:

Array Formula in cell E2:

How to create an array formula

1. Select cell E2
2. Type above array formula
3. Press and hold Ctrl + Shift
4. Press Enter once
5. Release all keys

How to copy array formula

1. Select cell E2
2. Copy (Ctrl + c)
3. Select cell range E3:E5
4. Paste (Ctrl + v)

Explaining formula in cell E2

There are two parts that calculate the position of the first instance and the second instance, the formula then subtracts the positions in order to get the distance.

Step 1 - Get position of second instance

SMALL(IF($C$2:$C$15=$E$3, MATCH(ROW($C$2:$C$15), ROW($C$2:$C$15)), ""), ROW(A1)+1)

becomes SMALL({1;"";"";4;"";"";7;"";"";"";"";"";"";14},2)

and returns 4. The second instance of 1 is found on row 4 (C5 is on row 4 in cell range $C$2:$C$15).

Step 2 - Get position of first instance

SMALL(IF($C$2:$C$15=$E$3, MATCH(ROW($C$2:$C$15), ROW($C$2:$C$15)), ""), ROW(A1))

becomes SMALL({1;"";"";4;"";"";7;"";"";"";"";"";"";14}, 1)

and returns 1. The first instance of 1 is found on row 1 (C2 is on row 1 in cell range $C$2:$C$15).

Step 3 - Subtract positions

SMALL(IF($C$2:$C$15=$E$3, MATCH(ROW($C$2:$C$15), ROW($C$2:$C$15)), ""), ROW(A1)+1)-SMALL(IF($C$2:$C$15=$E$3, MATCH(ROW($C$2:$C$15), ROW($C$2:$C$15)), ""), ROW(A1))-1

becomes

4-1-1

and returns 2 in cell G3.

How to remove #num errors

11. Count a specific text string in a cell

Question: How do I count how many times a text string exists in a cell value in Excel?

Answer:

The formula in cell C6 counts how many times a given text string is found in a cell value. The count is case sensitive meaning AA counts AA but not lower case aa, or aA etc.

Formula in C6:

11.1 Explaining formula in cell C6

Step 1 - Substitute given text string with nothing

SUBSTITUTE(C2, C4, "")

returns " BB CC CC BB CC ".

Step 2 - Count text string characters

LEN(SUBSTITUTE(C2, C4, ""))

becomes

LEN(" BB CC CC BB CC ")

and returns 17.

Step 3 - Count text string characters in cell C2

LEN(C2)

becomes

LEN("AA BB CC AA CC BB CC AA")

and returns 23.

Step 4 - Subtract original character length with new text string character length

LEN(C2)-LEN(SUBSTITUTE(C2, C4, ""))

becomes

23 - 17

and returns 6.

Step 5 - Divide with search string character length

(LEN(C2)-LEN(SUBSTITUTE(C2, C4, "")))/LEN(C4)

becomes

6/LEN(C4)

becomes

6/2

and returns 3 in cell C6.

11.2 Get Excel *.xlsx file

Count specific text string in a cell.xlsx

Back to top

12. Count text string in a cell range (case sensitive)

Question:

How do I count the number of times a text string exists in a column? The text string may exist multiple times in a cell, each instance is counted.

Answer:

Array formula in cell B11:

12.1 How to create an array formula

1. Copy array formula (Ctrl + c)
2. Select cell B11
3. Paste array formula (Ctrl + v) to formula bar
   
4. Press and hold Ctrl + Shift
5. Press Enter
6. Release all keys

You can also use this formula to count how many times a specific character exists in a column in excel.

12.2 Explain array formula in cell B11

=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

Step 1 - Replace existing text strings with new text string in named range tbl (A1:A6)

Substitute(text, old_text, new_text, [instance_num]) replaces existing text with new text in a text string

SUBSTITUTE(tbl, $B$9, "")

returns this array: {"";"BB CC B";... ;"NNBB  DD"}

Step 2 - Return the number of characters in the named range tbl (A1:A6) without text string "AA"

SUM(LEN(SUBSTITUTE(tbl, $B$9, "")))

and returns 44

Step 3 - Return the number of characters in the named range tbl (A1:A6)

SUM(LEN(A1:A6))

becomes

SUM({2;9;12;10;9;10})

returns 52.

Step 4 - Return the number of characters in cell B9

(SUM(LEN(A1:A6))-SUM(LEN(SUBSTITUTE(A1:A6, $B$9, ""))))/LEN($B$9)

LEN($B$9)

becomes

LEN("AA")

returns 2.

Step 5 - All together

(SUM(LEN(A1:A6))-SUM(LEN(SUBSTITUTE(A1:A6, $B$9, ""))))/LEN($B$9)

becomes (52-44)/2) becomes 8/2 and returns 4.

2.3 Get Excel file

count-text-string-in-a-column.xls

Back to top

13. Count a given pattern in a cell value - overlapping allowed

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even if it overlaps another match.

Formula in cell B6:

A regular count would result in 2 matches, see picture below.

A count where overlapping is allowed returns 3 matches and this is what is demonstrated in this article.

3.1 Explaining formula in cell B6

Step 1 - Build an array from 1 to the number of characters in the cell value

The LEN function counts the number of characters in cell B3.

LEN(B3) returns 13.

The INDEX function returns a cell reference based on a row number.

ROW(A1:INDEX(A1:A1000, LEN(B3)))

becomes

ROW(A1:INDEX(A1:A1000, 13))

becomes

ROW(A1:A13) and returns {1; 2; ... ; 13}

The ROW function returns the row number of a cell. If a cell range is used the ROW function returns an array of row numbers.

Step 2 - Extract all possible substrings from cell value

MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))

returns the following array:

Step 3 - Check if substring is equal to search string

MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3

returns {TRUE; FALSE; ... ; FALSE}.

Step 4 - Convert boolean values to the corresponding number

The SUMPRODUCT function can't handle boolean values so the SIGN function converts them into numbers. TRUE = 1 and FALSE = 0.

SIGN(MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3)

returns {1; 0; 1; ... ; 0}.

Step 5 - Count values in array

SUMPRODUCT(SIGN(MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3))

becomes SUMPRODUCT({1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0})

and returns 3 in cell B6. 1 + 0 + 1 + 0 + 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 = 3.

13.2 Get Excel *.xlsx file

Count overlapping text string in a cell.xlsx

Back to top

14. 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 to be an exact match but case sensitive. Column A1:A15 is the cell range.

Answer:

Cell E2 is the search string. In cell E3 an array formula counts the number of times the search string is found in cell range A1:A15.

Case sensitive formula in cell E3:

Explaining formula in cell E3

Step 1 - Count characters in each cell

The LEN function counts characters in a cell.

LEN(B2:B16)

returns {3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}

Step 2 - Substitue search string with nothing in all cells

The SUBSTITUTE function lets you replace a text string with another text string in a cell value or cell range.

SUBSTITUTE($B$2:$B$16, $E$2, "")

returns {"BBA";"CAC";... ;"BBCB"}

Step 3 - Count characters in array

LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))

returns {3;3;... ;4}

Step 4 - Subtract arrays

LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))

returns {0;0;4;... ;0}

Step 5 - Divide with cell length

(LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2

returns {0;0;0.5;...;0}

Step 6 - Sum number sin array

The SUMPRODUCT is better in this case because you are not required to enter the formula as an array formula to do the calculations.

SUMPRODUCT((LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2))

returns 5 in cell E2.

Case insensitive formula in cell E3:

Array formula in cell E6:

Get *.xlsx file

string exist in multiple cells.xlsx

15. How to count comma-separated values in a cell?

The following formula counts the number of strings in a single cell using a comma as a delimiting character.

Formula in cell C3:

Back to top

Explaining formula in cell C3

Step 1 - Substitute comma with nothing

The SUBSTITUTE function replaces a specific text string in a value.

SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE(B3,",","") returns "aaEE gg".

Step 2 - Count characters in the substituted text

The LEN function counts the number of characters in a string.

LEN(SUBSTITUTE(B3,",","")) returns 8.

Step 3 - Count characters in the original text

LEN(B3) returns 10.

Step 4 - Subtract original text length with substituted text length

LEN(B3)-LEN(SUBSTITUTE(B3,",",""))+1 becomes 10-8+1 equals 3.

Back to top

16. How to count comma-separated values in a cell range?

Array formula in cell C3:

How to enter an array formula

Enter the formula as a regular formula if you use Excel 365, follow these steps if you use an older version.

1. Copy above formula.
2. Double press with left mouse button on cell C3.
3. Paste to cell C3
4. Press and hold CTRL + SHIFT keys simultaneously.
5. Press Enter once.
6. Release all keys.

The formula will now look like this: {=SUM(LEN(B3:B8)-LEN(SUBSTITUTE(B3:B8, ",", ""))+1)}

Don't enter these characters yourself, they appear automatically.

Back to top

Explaining formula in cell C3

Step 1 - Substitute comma with nothing

The SUBSTITUTE function replaces a specific text string in a value.

SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE(B3:B8, ",", "")  returns {"aaEE gg";"jjoopp";"uuff bb";"uu";"xC Oy";"z OY RTEDSW"}.

Step 2 - Count characters in the substituted text

The LEN function counts the number of characters in a string.

LEN(SUBSTITUTE(B3:B8, ",", "")) returns {8; 6; 7; 2; 5; 11}.

Step 3 - Count characters in the original text

LEN(B3:B8) returns {10; 8; 9; 2; 6; 14}.

Step 4 - Subtract original text length with substituted text length

LEN(B3)-LEN(SUBSTITUTE(B3,",",""))+1 returns {3; 3; 3; 1; 2; 4}.

Back to top

17. How to count character-separated values in a cell?

The formula in cell C3 uses the characters given in cell E3 to separate and count values in B3. Cell E3 contains " | ", however, you can use whatever characters you want. For example, you can use this formula to separate and count values using a blank as a delimiting character.

Formula in cell C3:

Back to top

18. How to count the number of values separated by a delimiter - UDF

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

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 Editor (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.

Back to top

18.1 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

Back to top

18.2 Where to put the VBA code?

To build a user-defined function, follow these steps:

1. Press Alt + F11 to open the visual basic editor.
2. Press with left mouse button on "Insert" on the top menu.
3. Press with left mouse button on "Module", see the image above.
4. Copy the VBA code above and paste it to the code module.
5. Return to Excel.

Back to top

18.3 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.

Back to top

Count category

Count values category

More than 1300 Excel formulas

Excel categories

Comments (69)