I need to have it look at one cell at a time, but link 20 cells in the formula.
It is supposed to count the number cells that contain an X.
I can't use the range (K6:EN6) because of a similar =COUNTIF function that
will run on the same row.

Example:
=COUNTIF((K6,R6,Y6,AF6,AT6,BA6,BH6,BO6,BV6,CC6,CJ6 ,CQ6,CX6,DE6,DL6,DS6,EG6,EN6),"X")

I get the #VALUE! error when I try this.

I tried making each cell a range (J6:K6,Q6:R6) and so on, but still get the
same error.

You ought to receive input from some of the sharper advisors here, but for
the meantime I'd suggest you use a custom function like the one below

Function Count_X(rng As Range, strTest As String) As Integer
Dim cc As Range
Count_X = 0
For Each cc In rng
If cc.Value = strTest Then Count_X = Count_X + 1
Next
End Function

After pasting it into a module, you'd use it by entering this formula in a
cell

=count_x((K6,R6,Y6,AF6,AT6,BA6,BH6,BO6,BV6,CC6,CJ6 ,CQ6,CX6,DE6,DL6,DS6,EG6,EN6),"X")


"Xanadude" wrote:

I need to have it look at one cell at a time, but link 20 cells in the formula.
It is supposed to count the number cells that contain an X.
I can't use the range (K6:EN6) because of a similar =COUNTIF function that
will run on the same row.

Example:
=COUNTIF((K6,R6,Y6,AF6,AT6,BA6,BH6,BO6,BV6,CC6,CJ6 ,CQ6,CX6,DE6,DL6,DS6,EG6,EN6),"X")

I get the #VALUE! error when I try this.

I tried making each cell a range (J6:K6,Q6:R6) and so on, but still get the
same error.

How can I get the macro to run on all the worksheets? It only works on the
first one.
I get the same totals on all 17 worksheets!
I've never used one before!

"Duke Carey" wrote:

You ought to receive input from some of the sharper advisors here, but for
the meantime I'd suggest you use a custom function like the one below

Function Count_X(rng As Range, strTest As String) As Integer
Dim cc As Range
Count_X = 0
For Each cc In rng
If cc.Value = strTest Then Count_X = Count_X + 1
Next
End Function

After pasting it into a module, you'd use it by entering this formula in a
cell

=count_x((K6,R6,Y6,AF6,AT6,BA6,BH6,BO6,BV6,CC6,CJ6 ,CQ6,CX6,DE6,DL6,DS6,EG6,EN6),"X")


"Xanadude" wrote:

I need to have it look at one cell at a time, but link 20 cells in the formula.
It is supposed to count the number cells that contain an X.
I can't use the range (K6:EN6) because of a similar =COUNTIF function that
will run on the same row.

Example:
=COUNTIF((K6,R6,Y6,AF6,AT6,BA6,BH6,BO6,BV6,CC6,CJ6 ,CQ6,CX6,DE6,DL6,DS6,EG6,EN6),"X")

I get the #VALUE! error when I try this.

I tried making each cell a range (J6:K6,Q6:R6) and so on, but still get the
same error.