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
1	Name	COL1	COL2	COL3	COL4	COL5
2	Person x	x			x	x
3	Person x					x
4	Person x		x	x	x	x
5	5	1	1	1	1	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A5 =SUM(B5:F5) B5 =IF(COUNTIF(B$2:B$4,"*x*")>0,1,0) C5 =IF(COUNTIF(C$2:C$4,"*x*")>0,1,0) D5 =IF(COUNTIF(D$2:D$4,"*x*")>0,1,0) E5 =IF(COUNTIF(E$2:E$4,"*x*")>0,1,0) F5 =IF(COUNTIF(F$2:F$4,"*x*")>0,1,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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)