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

[Tinä]

Hi Frank,

Thanks for input. I've used this Formula referencing the cells wit
the A1-Style and works ok.

Can you explain the use of =1 in this Formula:
=MODE(IF(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$9)-ROW($A$2),0))=1,B2:B9))

I'VE TRIED TO AMEND THE FORMULA SLIGHTLY TO USE THE DYNAMIC NAMED RANG
WITHIN THE FORMULA - CAN YOU HELP
=MODE(IF(SUBTOTAL(3,OFFSET(INDEX(NAMEID,1,1),ROW(NAMEID)-MIN(ROW(NAMEID),0),1),RESULTS),,))???

WHEN I USE THIS REPLACED WITH MY A1-STYLE REFERENCES - I GET ZER
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))

*I do have text values * = your column "A" and my Dynamic Named Rang
NAMEID which contains the filtered elements.

Further help appreciated.

Thanks
Tinä

 

[Frank Kabel]

Hi
(1) The '1' indicates that the result of the subtotal function should
be one. As SUBTOTAL is only used with one single cell at a time the
result is either '0' (filtered') or '1' (#visible').

(2)a For using a defined name try the array formula:
=MODE(IF(SUBTOTAL(3,OFFSET(INDEX(range,1,1),ROW(range)-ROW(INDEX(range,
1,1)),0))=1,range+{0,0}))

Note: the +{0,0} part ensures that no error message is shown then your
most common value exists only once.

(2)b: Using mixed data (text + numeric): Now this is more complicated


I. For getting the most common entry without checking for filtered
values try the array formula:
=INDEX($B$2:$B$9,MODE(MATCH($B$2:$B$9,$B$2:$B$9,0)))
Note: Returns #NA then the range contains blank cells


And now for filtered row + range name:
=INDEX(range,MODE(IF(ISNA(MATCH(Filter,$B$2:$B$9,0)),"",MATCH(Filter,ra
nge,0))))

there range is your referred range and Filter is the defined name for
the following formula:
=IF(SUBTOTAL(3,OFFSET(INDEX(range,1,1),ROW(range)-ROW(INDEX(range,1,1))
,0))=1,range+{0.0})