Count Unique Visible Values with Criteria

[CokeOrCrack]

Hello:

Is there a formula that can count only the visible and unique values in a column, based on criteria from another column?

This array formula I have counts the unique values with criteria, but it ignores filters and counts non-visible cells.

{=SUM(--(FREQUENCY(IF(Clean[Date]<>"",IF(Clean[Year]=YEAR(NOW()),MATCH(Clean[Date],Clean[Date],0))),ROW(Clean[Date])-ROW(INDIRECT("D15"))+1)>0))}

Thanks

OJ

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL([COLOR=#ff0000]3[/COLOR],OFFSET(Clean[Date],ROW(Clean[Date])-MIN(ROW(Clean[Date])),0,1))>0,IF(Clean[Year]=YEAR(NOW()),MATCH(Clean[Date],Clean[Date],0))),ROW(Clean[Date])-MIN(ROW(Clean[Date]))+1)>0,1))

However, if the year in Clean[Year] will always match the year in Clean[Date], the second condition can be omitted as follows...

=SUM(IF(FREQUENCY(IF(YEAR(SUBTOTAL([COLOR=#ff0000]9[/COLOR],OFFSET(Clean[Date],ROW(Clean[Date])-MIN(ROW(Clean[Date])),0,1)))=YEAR(NOW()),MATCH(Clean[Date],Clean[Date],0)),ROW(Clean[Date])-MIN(ROW(Clean[Date]))+1)>0,1))

Note, if you also want to exclude rows that have been manually hidden, replace the 3 and 9 in red with 103 and 109, respectively.

Also, note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[CokeOrCrack]

Domenic:

Works perfect!

Thanks

 

[Domenic]

You're very welcome, cheers!