Find most frequently occurring text string in in a VISIBLE range of cells

Hi,

Are you able to be a bit more specific about your actual requirements? The information you've given is vague, to say the least. Perhaps an attachment with some examples and your desired results would help?

Regards

Hi,

I noticed in the attachment that your list was not actually filtered, but contained hidden rows, which requires a slightly different formula. If it is filtered cells, and not hidden, that you require, change the first parameter in the SUBTOTAL function below from 103 to 3.

I had to create a Defined Name to overcome the formula nesting limitations in Excel 2003. So, first of all, go to Name Manager and define a new name, say Array1, with the following formula:

=SUBTOTAL(103,OFFSET(Sheet1!$A$3:$A$49,ROW(Sheet1!$A$3:$A$49)-ROW(Sheet1!$A$3),,1,))

(Note: this formula, and the next, are based on the range you gave in the attached - obviously amend to suit your actual requirements.)

Exit Name Manager and then enter this array formula (make sure you know how to enter these in Excel - they are NOT the same as regular formulas) in some cell:

=INDEX($A$3:$A$49,MATCH(MAX(MMULT(TRANSPOSE(ROW($A$3:$A$49)^0),--(IF(Array1,$A$3:$A$49,0)=TRANSPOSE(IF(Array1,$A$3:$A$49))))),MMULT(TRANSPOSE(ROW($A$3:$A$49)^0),--(IF(Array1,$A$3:$A$49,0)=TRANSPOSE(IF(Array1,$A$3:$A$49)))),0))

Regards