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?



[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]
 
Re: How to count the number of Xs in a cell

OK, here is one version of a UDF to do that:
Code:
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)
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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?
 
Upvote 0
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?
 
Upvote 0
Here is another UDF (user defined function) that you can try...
Code:
Function CountX(HorzRange As Range) As Long
  CountX = UBound(Split(Join(Application.Index(HorzRange.Value, 1, 0)), "x", , vbTextCompare))
End Function
 
Upvote 0
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.
 
Upvote 0
Hi Rick, I tried it and it worked!
It's counting every single X in each cell...thanks so much!!!
 
Upvote 0
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]
 
Upvote 0
Hi,

Maybe you have the calculation set on the manual.
If you press F9, the formula calculates correctly?
 
Upvote 0
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...
Code:
Function CountX(HorzRange As Range) As Long
  Application.Volatile
  CountX = UBound(Split(Join(Application.Index(HorzRange.Value, 1, 0)), "x", , vbTextCompare))
End Function
 
Upvote 0
Hi Rick,

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

Forum statistics

Threads
1,223,992
Messages
6,175,826
Members
452,673
Latest member
LaMiaAvy

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