I may be missing something here but can't see why that matters. The macro
runs through each cell in the selection of cells you put in to the function,
which from the look of it is every 7th column. It then checks each cell to
see if it has an X in it, and if so it counts it. The formula does the
same, and doesn't matter what else is in between or also querying the same
range? You can put the formula in any column outside your range so that
shouldn't be a limitation. I was assuming you would simply put the formula
wherever you had your function.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"Xanadude" wrote in message
...
My bad...
Forgot to include:
I can't use the range (K6:EN6) because of a similar =COUNTIF function that
runs on the same row.
"Ken Wright" wrote:
How about a formula instead as opposed to a UDF, that will take a range
as
opposed to multiple arguments:-
=SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=4),--(K6:EN6="X"))
This assumes that you don't vary the columns in anyway though, ie not
adding
or deleting columns, including any before the range starts in Cols A:J.
If
that may happen, then you could always use:-
=SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=MOD(COLUMN(K6),7)),--(K6:EN6="X"))
but this still assumes no variation of columns inside your range, ie
there
are currently 6 columns between each possible X.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--
"Xanadude" wrote in message
...
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,AM6,AT6,BA6,BH6,BO6,BV6,CC6 ,CJ6,CQ6,CX6,DE6,DL6,DS6,D
Z6,EG6,EN6),"X")
I did this while the rest of my spreadsheet was still imcomplete. It
worked
on 1 page, but not the other 16 that I needed it used on. I decided to
delete
it temproarily until the rest of the sheets are completed.
They are now done, and after putting the macro back in, it's not doing
anything. What am I doing wrong? I've never used a macro before, and
like
most help sections... it's written in Greek.
|