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]

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

Me too!

 

[Rick Rothstein]

Hi Rick,

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

Me too!

I did not think it was necessary either but I was going out and did not have time to test it, so I just went with the OP's comment and assumed it must have been need this time. If as your testing shows it is not needed, then I am not sure why the OP had a problem.

 

[Joe4]

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

I did not think it was necessary either but I was going out and did not have time to test it, so I just went with the OP's comment and assumed it must have been need this time. If as your testing shows it is not needed, then I am not sure why the OP had a problem.

Yes, there appear to be a a few oddities, along with them being unable to get my UDF to work, but yours does.

Makes me wonder which version of Excel are they using, and is it for Windows or for Mac?

 

[Manny74]

Well I added two more Xs to see if my total Xs would change and it did not...
So I pressed F9 and I get #NAME ?

 

[Manny74]

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

i have MS Excel 2013; Windows 7 Enterprise;

 

[Manny74]

So it seems once I save the worksheet, close it, reopen it...If I add 2 more Xs, click on the CountX formula and press F9 I get #NAME ?
Is this because when I used the VB code you give me, I cant save it?

 

[Joe4]

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

The #NAME error implies it is not seeing the UDF.
Can you confirm exactly where you are saving the UDFs?
Are you storing them in a new module within that particular workbook?
And are you usre that you are enabling macros/VBA? It won't work if they are not enable when you open the workbook.

 

[Joe4]

So it seems once I save the worksheet, close it, reopen it...If I add 2 more Xs, click on the CountX formula and press F9 I get #NAME ?
Is this because when I used the VB code you give me, I cant save it?

Yes, you need to save it. You should be able to do that, if you are able to save the Workbook.
And you need to make sure that you enable macros/VBA each time.

 

[mohadin]

Hi
May be

Function Cxs(rng As Range) As Long
    Dim cell As Range
    Dim a As Variant
    a = Join(Application.Transpose(WorksheetFunction.Transpose(rng)))
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(\X)|(\x)"
        Set m = .Execute(a)
        Cxs = m.Count
    End With
End Function

=Cxs(B2:M2)