Count Same Or Duplicate Values Only Once

[andyzz]

Hi everyone,

I'm using this formula to count same or duplicate values only once.
As of now its working fine for a specified range i.e E48:E57
Whereas I want it to work for a visible range in a table when filters are applied.

Secondly, I want to exclude a term 'CODE' if it ever appears in a column.

=SUMPRODUCT((E48:E57<>"")/COUNTIF(E48:E57,E48:E57&""))

Can someone please make it work or may be you can write a new code.

Thanks

[andyzz]

Can someone please make it work. thanks

[63falcondude]

You will have to incorporate the SUBTOTAL function if you want to exclude hidden rows from your calculation.

If you would like more detailed assistance, please upload a small representative sample of your data along with the desired result (manually entered) of a formula.

[kvsrinivasamurthy]

Here is the code for UDF CountUnique

Please Login or Register  to view this content.

[andyzz]

@kvsrinivasamurthy

Bang on. It worked perfect. Thanks a ton. Reputation Added.!

[kvsrinivasamurthy]

Thanks for reputations.