View Single Post
  #4   Report Post  
Old May 17th 05, 12:02 AM
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Domenic,

I've taken another look at my scenario and what I'm trying to achieve with
the formula. I think if the cell references could possibly be replaced by
the use of the OFFSET and SUBTOTAL functions, perhaps the Count results
returned would reflect only the visible filtered data - maybe.

My thoughts behind using OFFSET is to negate the need for the current hard
coded cell referencing so that the formula sees the Rows of data in the
Column purely from a position of "Row above" and "Row below" or "Row below"
and "Row above." So that the actual cell Row reference will be immaterial,
if that's possible and the count will provide the correct results for
visible filtered data?

Is the above possible?

Further help appreciated.
------------------------------
While I'm not able to offer a solution for filtered data, I can offer one
for non-filtered data. Assuming that Column C contains your
'Names'...

D11, copied down:

=IF(C11=C10,0,IF(COUNTIF($C$11:C11,C11)-1,(ROW()-ROW($C$11))-LARGE(IF($C$
11:C11=C11,ROW($C$11:C11)-ROW($C$11)+1),2),ROW()-ROW($C$11)))

....confirmed with CONTROL+SHIFT+ENTER.
-------------------------------

Thanks
Sam

--
Message posted via http://www.officekb.com