Count only visible results of formula

J

Joe M.

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.
 
J

Joe M.

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

Peo Sjoblom

Try


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

--


Regards,


Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count only visible formula results 2
Count only visible cells 4
Determine a result of one column based on conditions in two column 1
Count of visible, non-blank cells only? 8
Why doesn't excel count function update automatically? 1
Counting blank and filled cells within a range. 3
Count a column 3
help with formula range 9

Top