IF COUNTIF & COUNTA on Filtered Visible Cells #3

Hi Aladin,

Thank you for assistance.  Unfortunately, the formula is not retrievin
the correct results.

The formulae below tells me how frequently the ROOMS are being used b
different Groups and how long they have not used them. The formula
works ok on non-filtered data but when I use Autofilter the results ar
not as they should be, because the results also include the non-filtere
data rather than just the Filtered Visible Cells.

Using the Formulae below, I can obtain the required data from th
Columns in a non-filtered state.

Column T:
=IF(COUNTIF($V$10:$V10,$V11),COUNTA(INDIRECT("V"&U11+1&":V"&ROW()))-1,COUNTA($V$10:$V10))

Column U (helper column):
=MAX(IF($V$10:$V10=$V11,ROW($V$10:$V10)))

Column V:
Text Data

Further help appreciated.

Thanks
Tin�

Aladin Akyurek Wrote:
> If you're trying to count the occurrences of a certain text in V whic
> is part of an AutoFilter'ed range....
> 
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),--(Vrange="Rome"))
> 
> would calculate the frequency of occurrence of "Rome" in Vrange, th
> range in column V in the area subjected to AutoFilter

--
Tin
-----------------------------------------------------------------------
Tin�'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1541
View this thread: http://www.excelforum.com/showthread.php?threadid=27428

0
11/1/2004 9:06:04 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
394 Views

Similar Articles

[PageSpeed] 40

Reply:

Similar Artilces: