Alter Formula for unique value to sum only visible cells

[hrayani]

Hello Friends,

I am using this formula to count unique values in a range

=SUM(IF(FREQUENCY(IF(LEN(orders_ref)>0,MATCH(orders_ref,orders_ref,0),""),IF(LEN(orders_ref)>0,MATCH(orders_ref,orders_ref,0),""))>0,1))

I want this formula to count only visible cells....

If anyone can provide a solution... Like alter or change it a bit.


Regards,

Humayun

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(102,OFFSET(B4,ROW(B4:B20)-ROW(INDEX(B4:B20,1,1)),0)),IF(B4:B20<>"",MATCH(B4:B20,B4:B20,0))),ROW(B4:B20)-ROW(INDEX(B4:B20,1,1))+1),1))


Use 2 or 103 as function num.

I meant either 2 or 102...

 

[hrayani]

Hi Aladin,

Many Thanks... Its working perfect now

 

[Aladin Akyurek]

Hi Aladin,

Many Thanks... Its working perfect now

Happy to hear that.