Count the number of Xs in a cell

[Manny74]

Hello All,

I need a formula that will allow me to count the number of Xs in a cell....The total Xs should be 5, but when I use the formula, COUNTA(B2:M2) , it counts 3.... I tried COUNTIF(B2:M2,"X") but it also counted only 3....

What formula can I use to count the number of Xs in each cell?



[TABLE="width: 510"]
<colgroup><col><col><col span="12"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD] B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Shift[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7 AM[/TD]
[TD] [/TD]
[TD]XXX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Re: How to count the number of Xs in a cell

OK, here is one version of a UDF to do that:

Function CountX(rng As Range) As Long
'   Counts the number of X's in a specified range
    Dim cell As Range
    Dim temp As Long
    
    For Each cell In rng
        temp = temp + (Len(cell) - Len(Replace(cell, "X", "")))
    Next cell
    
    CountX = temp
    
End Function

So, if you put that in a new VBA module in your workbook and save it, you will be able to use it like any other Excel function on your sheets, i.e.
=CountX(B2:M2)

 

[Manny74]

Re: How to count the number of Xs in a cell

Ok so I pressed ALT F11, copied your code, clicked on insert, clicked on module, pasted your code, then I close the Microsoft Visual Basic for Applications window, but when I try to create a formula, =CountX, it doesn't seem to be an option?

 

[Rick Rothstein]

Re: How to count the number of Xs in a cell

Ok so I pressed ALT F11, copied your code, clicked on insert, clicked on module, pasted your code, then I close the Microsoft Visual Basic for Applications window, but when I try to create a formula, =CountX, it doesn't seem to be an option?

It works for me. Are you sure you do not see it in the list of macros that appears when you start typing =C into the cell?

 

[Rick Rothstein]

Here is another UDF (user defined function) that you can try...

Function CountX(HorzRange As Range) As Long
  CountX = UBound(Split(Join(Application.Index(HorzRange.Value, 1, 0)), "x", , vbTextCompare))
End Function

 

[Joe4]

Ok so I pressed ALT F11, copied your code, clicked on insert, clicked on module, pasted your code, then I close the Microsoft Visual Basic for Applications window, but when I try to create a formula, =CountX, it doesn't seem to be an option?

Make sure that you are inserting the module in the proper workbook.

 

[Manny74]

Hi Rick, I tried it and it worked!
It's counting every single X in each cell...thanks so much!!!

 

[Manny74]

Hi Rick, so something odd is happening...
So I add a formula, CountX(A2:H2) and I get 4...this is correct
If I add a X to any cell on row 2, the total (4) does not change....Why is that?

[TABLE="width: 299"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]XXX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tom.Jones]

Hi,

Maybe you have the calculation set on the manual.
If you press F9, the formula calculates correctly?

 

[Rick Rothstein]

Hi Rick, so something odd is happening...
So I add a formula, CountX(A2:H2) and I get 4...this is correct
If I add a X to any cell on row 2, the total (4) does not change....Why is that?

Try this modified version of my function...

Function CountX(HorzRange As Range) As Long
  Application.Volatile
  CountX = UBound(Split(Join(Application.Index(HorzRange.Value, 1, 0)), "x", , vbTextCompare))
End Function

 

[Tom.Jones]

Hi Rick,

For me your VBA code is working without Application.Volatile.
Each time I add or delete an X the result changes.