Countif unique values for visible cells

[jakjok]

Hi

I want to retrieve the Countif unique values for visible cells

so for Countif unique values its working fine using this query:

=SUM(IF(D2:D3134<>"",1/COUNTIF(D2:D3134,D2:D3134), 0))

i tried to add the point for having that working only for filtered cells with luck.

 

[richh]

I think subtotal or your aggregate function would work.

 

[jakjok]

i tried these tow way with no luck.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D3134,ROW(D2:D3134)-MIN(ROW(D2:D3134)),,1)),--(SUM(IF(D2:D3134<>"",1/COUNTIF(D2:D3134,D2:D3134), 0))))
=SUM(IF(D2:D3134<>"",1/SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D3134,ROW(D2:D3134)-MIN(ROW(D2:D3134)),,1))), 0))

 

[richh]

Try =AGGREGATE(3,3, [Array])

 

[Fluff]

You cannot use arrays in Aggregate unless you are using option 14 to 19.

 

[Eric W]

Try:

=SUM(SIGN(FREQUENCY(IF((D2:D3134<>"")*SUBTOTAL(103,OFFSET(D2,ROW(D2:D3134)-ROW(D2),0)),MATCH(D2:D3134,D2:D3134,0)),ROW(D2:D3134)-ROW(D2)+1)))

Depending on the update level of your Excel 365, you may or may not need to confirm that with Control+Shift+Enter.

 

[Fluff]

Another option if you have dynamic arrays
=COUNT(FILTER(ROW(D2:D3134),(D2:D3134<>"")*(SUBTOTAL(103,OFFSET(D2,ROW(D2:D3134)-ROW(D2),0)))))

 

[Eric W]

Fluff, I think you need a UNIQUE in there, and a couple other tweaks:

=COUNTA(UNIQUE(FILTER(D2:D3134,(D2:D3134<>"")*(SUBTOTAL(103,OFFSET(D2,ROW(D2:D3134)-ROW(D2),0))))))

 

[Fluff]

Oops,
Missed the bit about unique values.

 

[Fluff]

I prefer to use Rows, rather than Counta in case there is a chance that all visible cells in the range are blank.
Rows will return an error, whereas Counta will return 1
=ROWS(UNIQUE(FILTER(D2:D3134,(D2:D3134<>"")*(SUBTOTAL(103,OFFSET(D2,ROW(D2:D3134)-ROW(D2),0))))))