This post will guide you how to **count the total number of cells with string length in a specified range** in Excel. How to get the number of cells that its length is greater than a specific number in Excel.

Table of Contents

## Method 1: Excel Formula – SUMPRODUCT Function

For Example, if you have a range of cells in B1:B5 and each cell contains one text string value have different string length. And you want to count the number of the string length of each cell is greater than number 10.

You can create a new formula based on the SUMPRODUCT function, the N function and the LEN function to check the string length of each cell and also get the total number that the string length is greater than number 10. So let’s write down the following formula:

**=SUMPRODUCT(N(LEN(B1:B5)>10))**

Or

**=SUMPRODUCT(--(LEN(B1:B5)>10))**

Then type this formula in the formula box of Cell C1, then press **Enter** key, you will see the result.

## Method 2: VBA Code

In our second method, we’ll dive into the world of VBA scripting. This approach provides enhanced flexibility and control for custom functions.

Press **Alt + F11 **to open the Visual Basic for Applications (VBA) editor.

In the VBA editor, right-click on any item in the Project Explorer, choose “**Insert**,” and then “**Module**.”

Copy and paste the following VBA code into the module:

```
Function CountStringsAboveLength(rng As Range, length As Integer) As Long
Dim cell As Range
Dim count As Long
For Each cell In rng
If Len(cell.Value) > length Then
count = count + 1
End If
Next cell
CountStringsAboveLength = count
End Function
```

Close the VBA editor and return to your Excel workbook. In a new cell, enter the formula:

`=CountStringsAboveLength(B1:B5, 10)`

adjusting the range and length as needed.

After entering the formula, press Enter to execute it. The result will display the count of cells meeting the specified length criterion.

## 3. Video: Count Number of Cells with String Length greater than X

This Excel video tutorial where we explore two efficient methods to count cells with string lengths exceeding a set value. Method 1 employs a formula with the SUMPRODUCT function, while Method 2 utilizes VBA scripting for added flexibility.

## 4. Related Functions

- Excel SUMPRODUCT function

The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:**=**SUMPRODUCT (array1,[array2],…)… - Excel LEN function

The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:**=**LEN(text)… - Excel N function

The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:**=**LEN(text)…

## Leave a Reply

You must be logged in to post a comment.