Unique value count on sorted coloumn

[merlin_the_magician]

Hello,

I'm struggling with a workbook that holds quite a lot of data. I would like to count unique values in column AX, whereas i also would like to be able to count unique values in case a sorting filter is used.

I made a matrix-formula for the general unique values count, that works perfectly:
{=SUM(IF(FREQUENCY(IF(LEN(AX9:AX414)>0,MATCH(AX9:AX414,AX9:AX414,0),""),IF(LEN(AX9:AX414)>0,MATCH(AX9:AX414,AX9:AX414,0),""))>0,1))}

I tried using =SUBTOTAL for the unique values count like this, but....
{=SUBTOTAL(9,(IF(FREQUENCY(IF(LEN(AX9:AX414)>0,MATCH(AX9:AX414,AX9:AX414,0),""),IF(LEN(AX9:AX414)>0,MATCH(AX9:AX414,AX9:AX414,0),""))>0,1))}
... that doen't work. The result is #VALUE

Any thoughts on this one please?

 

[Canapone]

Hi,

a first attempt

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($AX$9:$AX$414,ROW($9:$414)-ROW($A$9),0,1)),MATCH($AX$9:$AX$414,$AX$9:$AX$414,0)),ROW(9:414)-ROW($A$9)+1)>0,--(LEN(AX$9:$AX$414)>0)))

Array entered

Regards