I am using =IF(ISERR(SEARCH("*Z*",A6)),"","X") in column M to display an "X"
if the charactor "Z" is found in col A. I am using autofilter so at times
some of the rows are not visible. I have tried using =SUBTOTAL(3,M6:M2000)
but it counts all cells in col M regardless if it has an "X" or not. I think
it is counting the cells because it is filled withe a formula. What formula
can I use to count ONLY the VISIBLE cells with "X"?

Many thanks,
Joe M.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(M6:M2000,ROW(M6:M200 0)-ROW(M6),0,1)))


"Joe M." wrote:

I am using =IF(ISERR(SEARCH("*Z*",A6)),"","X") in column M to display an "X"
if the charactor "Z" is found in col A. I am using autofilter so at times
some of the rows are not visible. I have tried using =SUBTOTAL(3,M6:M2000)
but it counts all cells in col M regardless if it has an "X" or not. I think
it is counting the cells because it is filled withe a formula. What formula
can I use to count ONLY the VISIBLE cells with "X"?

Many thanks,
Joe M.

Doesn't work. Still counts all of M with formula regardless of X or blank
result.

"Teethless mama" wrote:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(M6:M2000,ROW(M6:M200 0)-ROW(M6),0,1)))


"Joe M." wrote:

I am using =IF(ISERR(SEARCH("*Z*",A6)),"","X") in column M to display an "X"
if the charactor "Z" is found in col A. I am using autofilter so at times
some of the rows are not visible. I have tried using =SUBTOTAL(3,M6:M2000)
but it counts all cells in col M regardless if it has an "X" or not. I think
it is counting the cells because it is filled withe a formula. What formula
can I use to count ONLY the VISIBLE cells with "X"?

Many thanks,
Joe M.

Try


=SUMPRODUCT(--(M6:M2000="X"),--(SUBTOTAL(3,OFFSET($M$6,ROW(M6:M2000)-MIN(ROW(M6:M2000)),,))))

--


Regards,


Peo Sjoblom

"Joe M." wrote in message
...
Doesn't work. Still counts all of M with formula regardless of X or blank
result.

"Teethless mama" wrote:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(M6:M2000,ROW(M6:M200 0)-ROW(M6),0,1)))


"Joe M." wrote:

I am using =IF(ISERR(SEARCH("*Z*",A6)),"","X") in column M to display
an "X"
if the charactor "Z" is found in col A. I am using autofilter so at
times
some of the rows are not visible. I have tried using
=SUBTOTAL(3,M6:M2000)
but it counts all cells in col M regardless if it has an "X" or not. I
think
it is counting the cells because it is filled withe a formula. What
formula
can I use to count ONLY the VISIBLE cells with "X"?

Many thanks,
Joe M.