|
|
| |
 Question |
|
| |
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.
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
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.
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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.
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
Notify me of replies