Update formula to count unique visible

[tezza]

HI All

Hope you can help.

I've got this formula below in a spreadsheet:

=COUNTA(UNIQUE($M$2:$M$10231))

How do I update this to count the visible unique values in the same range?

I've tried what I figured to be a logical solution (as 103 = counta) but it just throws up an error:

=subtotal(103,UNIQUE($M$2:$M$10231))

Regards
Tez

edit: 365 version

 

[LazyBug]

Maybe

=AGGREGATE(3,7,UNIQUE($M$2:$M$10231))

 

[tezza]

Maybe

=AGGREGATE(3,7,UNIQUE($M$2:$M$10231))

HI, that's shows a #VALUE! for wrong data type. My data is text if that helps.

 

[LazyBug]

HI, that's shows a #VALUE! for wrong data type. My data is text if that helps.

It was just a guess, my fault, I can't reconstruct situation on Excel 2010 with example data because of absence of UNIQUE function in this version.
Lets wait for an answer from other forum users...

 

[tezza]

I've no idea how this works but it does, found it in another thread here and adapted it to my sheet:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(M2:M10231,ROW(M2:M10231)-ROW(M2),0,1)), IF(M2:M10231>"",MATCH("~"&M2:M10231,M2:M10231&"",0))),ROW(M2:M10231)-ROW(M2)+1),1))

I'm baffled by the complexity of it lol but will keep it here for safe keeping.

 

[LazyBug]

I've no idea how this works but it does

Congratulations!