Count number of cells with more than 3 charactors

jbanks · Jan 27th 2021

Hi

Hopefully an easy one, I am trying to count across a row the number of times a cell contains more than 3 charactors.

I have tried count if with a Len function and a sumproduct but keep getting a #value error returned.

Thank you

Jeff

**alansidman** · Jan 27th 2021

Here is a VBA solution for you assuming your data starts in cell A1

Code

Option Explicit


Sub Count3()
Dim lr As Long
Dim lc As Long
Dim x As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Dim i As Long, j As Long
For i = 1 To lr
x = 0
For j = 1 To lc
If Len(Cells(i, j)) > 3 Then
x = x + 1
End If
Next j
Cells(i, lc + 1) = x
Next i
MsgBox "completed"
End Sub

Display More

**Carim** · Jan 27th 2021

Hello,

What formula are you using ...???

Have you tried :

Code

=SUMPRODUCT(N(LEN(A1:Z1)>3))

Hope this will help

jbanks · Jan 30th 2021

thank you

I was missing the N part!

Works perfectly

**Carim** · Jan 30th 2021

Glad you could fix your problem ...

Thanks for your Thanks ... AND for the Like

Count number of cells with more than 3 charactors

Participate now!

Share