COUNT unique values after filter

[afountas]

Hello,

I have a table column with 200 enties, 65 which are unique. I need a formula to be able to count only the unique values even after a filter has been applied.

I have this so far, which only counts the unique values, but will not adjust to a filter.

=SUM(IF(FREQUENCY(Table2[DMA Rank],Table2[DMA Rank])>0,1))

Any Ideas?

 

[bolandd]

The subtotal function should be used instead of the sum function.

The following should point you in the right direction.

=SUBTOTAL(1,IF(FREQUENCY(Table2[DMA Rank],Table2[DMA Rank])>0,1))

 

[afountas]

That formula returned #VALUE!

any fix?

 

[barry houdini]

Try this version

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Table2[DMA Rank],ROW(Table2[DMA Rank])-MIN(ROW(Table2[DMA Rank])),1)),MATCH(Table2[DMA Rank],Table2[DMA Rank],0)),ROW(Table2[DMA Rank])-MIN(ROW(Table2[DMA Rank]))+1),1))

confirmed with CTRL+SHIFT+ENTER

 

[afountas]

2nd Version doesnt return adjusted value after filter is applied : (

 

[barry houdini]

Are you sure? I tested the formula and it works OK for me....

What value do you get? Are you applying CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula?

 

[afountas]

Hey Barry,

Before the filter, I get a value of 65, which is good. But the formula doesnt react to the filter.

Also, I did make sure to activate the formula with the CTRL +SHIFT +ENTER command

 

[barry houdini]

Apologies, badly tested!

I missed a crucial comma in the OFFSET function - should be like this

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Table2[DMA Rank],ROW(Table2[DMA Rank])-MIN(ROW(Table2[DMA Rank])),,1)),MATCH(Table2[DMA Rank],Table2[DMA Rank],0)),ROW(Table2[DMA Rank])-MIN(ROW(Table2[DMA Rank]))+1),1))

 

[afountas]

Awesome,

Thank you so much.