Counting Columns of x

[Stephen_IV]

I have been racking my brain. I am trying to get a formula to count columns that contain an x. If it does I only want to column to be counted as 1. For the example below I want a count of 5.
Thanks in advance!

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Name</th><th>COL1</th><th>COL2</th><th>COL3</th><th>COL4</th><th>COL5</th></tr></thead><tbody>
<tr><td>Person x</td><td>x</td><td> </td><td> </td><td>x</td><td>x</td></tr>
<tr><td>Person x</td><td> </td><td> </td><td> </td><td> </td><td>x</td></tr>
<tr><td>Person x</td><td> </td><td>x</td><td>x</td><td>x</td><td>x</td></tr>
</tbody></table>

 

[Toadstool]

How about:

	A	B	C	D	E	F
Person x
Person x
Person x

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]COL1[/TD]
[TD="align: center"]COL2[/TD]
[TD="align: center"]COL3[/TD]
[TD="align: center"]COL4[/TD]
[TD="align: center"]COL5[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]=SUM(B5:F5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=IF(COUNTIF(B$2:B$4,"*x*")>0,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=IF(COUNTIF(C$2:C$4,"*x*")>0,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]=IF(COUNTIF(D$2:D$4,"*x*")>0,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=IF(COUNTIF(E$2:E$4,"*x*")>0,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=IF(COUNTIF(F$2:F$4,"*x*")>0,1,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Stephen_IV]

Hi Toadstool thanks but I am looking for 1 formula to accomplish this.

 

[Joe4]

Hi Toadstool thanks but I am looking for 1 formula to accomplish this.

One way is to add up those individual formulas Toadstool created in one big, long formula:

=[COLOR=#333333]IF([/COLOR][COLOR=Blue]COUNTIF([COLOR=Red]B$2:B$4,"*x*"[/COLOR])>0,1,0[/COLOR][COLOR=#333333])+[/COLOR][COLOR=#333333]IF([/COLOR][COLOR=Blue]COUNTIF([COLOR=Red]C$2:C$4,"*x*"[/COLOR])>0,1,0[/COLOR][COLOR=#333333])+[/COLOR][COLOR=#333333]IF([/COLOR][COLOR=Blue]COUNTIF([COLOR=Red]D$2:D$4,"*x*"[/COLOR])>0,1,0[/COLOR][COLOR=#333333])+[/COLOR][COLOR=#333333]IF([/COLOR][COLOR=Blue]COUNTIF([COLOR=Red]E$2:E$4,"*x*"[/COLOR])>0,1,0[/COLOR][COLOR=#333333])+[/COLOR][COLOR=#333333]IF([/COLOR][COLOR=Blue]COUNTIF([COLOR=Red]F$2:F$4,"*x*"[/COLOR])>0,1,0[/COLOR][COLOR=#333333])[/COLOR]

 

[Paul Ked]

Adapt the above to:

=(IF(COUNTIF(B$2:B$4,"*x*")>0,1,0))+(IF(COUNTIF(C$2:C$4,"*x*")>0,1,0))+(IF(COUNTIF(D$2:D$4,"*x*")>0,1,0))... etc

 

[alansidman]

Taking Toadstool's approach, I incorporated into a VBA solution.

Option Explicit


Sub CntX()
    Dim lc As Long, i As Long
    Dim a As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    a = 0
    For i = 2 To lc
        If WorksheetFunction.CountIf(Columns(i), "x") > 0 Then
            a = a + 1
        End If
    Next i
    MsgBox ("Columns Counted were " & a)


End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Joe4]

If you like the VBA route, you could also created your own User Defined VBA function to do it.
The advantage there is ease of re-use (instead of having to use a long formula).

 

[Stephen_IV]

Thank you all for the help. Joe4 I am not great with VBA although alansidman gave me a subroutine and I appreciate it, a VBA function could be nice also!

 

[anthonyexcel]

If you need a function try this

Function countx(r As Range)
Dim xcell As Range
Dim i As Long
    For Each xcell In r.Columns
         If WorksheetFunction.CountIf(xcell, "x") > 0 Then
            i = i + 1
        End If
    Next
countx = i
End Function

Use it like =countx(B2:F4)