Formula to Count and Return Most common Value in a Dynamic Named Range

[Tinä]

Hi,

I'm looking for a Formula to return the *most frequent * value in
Dynamic Named Range that will be filtered. I therefore, need th
Formula to include *only* the Visible cell values of the Dynamic Name
Range called RESULTS.

The Formula needs to :

1) Return the MOST frequent value in (Filtered Visible Cells) Range ;
2) Return the Number of Times this value appears in (Filtered Visibl
Cells) Range.

The Dynamic Named Range details for RESULTS in the Define Name Refer
To Box:
=OFFSET('SUBJECT'!$Q$9,2,0,COUNTA('SUBJECT'!$Q:$Q),1)

Thanks
Tin

 

[Frank Kabel]

Hi
for numeric values use something like the following array formula
(entered with CTRL+SHIFT+ENTER):
=MODE(IF(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$9)-ROW($A$2),0))=1,B2:B9))

B1:B9 is the range of interest (containing your numeric values), column
A contains the filtered elements.

For counting the number of values lets assume you have entered the
above formula in C1. Then use:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$2:$A$9,ROW($A$2:$A$9)-ROW($A$2),0))
=1),--($B$2:$B$9=C1))

If you need this for text values (or mixed values) please post back