Count the number of Xs in a cell

Manny74

Board Regular
Joined
May 6, 2016
Messages
124
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?



A BCDEFGHIJKLM
1Shift123456789101112
27 AM XXX X X

<colgroup><col><col><col span="12"></colgroup><tbody>
</tbody>
 
For me your VBA code is working without Application.Volatile.
Each time I add or delete an X the result changes.
Me too!
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi Rick,

For me your VBA code is working without Application.Volatile.
Each time I add or delete an X the result changes.
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.
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0
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 ?
 
Upvote 0
Re: How to count the number of Xs in a cell

i have MS Excel 2013; Windows 7 Enterprise;
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Hi
May be

Code:
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)
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,708
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top