Filtered Formula Field

[SantasLittleHelper]

I have the formula below to calculate the unique values in the specified cells.

=SUM(IF(FREQUENCY(H5:H3200,H5:H3200)>0,1))

When I apply a filter to a column on the spreadsheet, I'd like it to change the value.

E.g. The formula returns 100 when unfiltered. If I filter to show any data where the month is May 2018, the number should show 15.

Is this possible?

 

[James006]

Hello,

You can test the following Array Formula

SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(H5,ROW(H5:H3200)-ROW(H5),,1)), IF(H5:H3200<>"",MATCH("~"&H5:H3200,H5:H3200&"",0))),ROW(H5:H3200)-ROW(H5)+1),1))

Hope this will help