Re: Help with which COUNT function to use

[kullie009]

Re: Help with which COUNT function to use

Please could someone help me with this query (it probably really simple!)

Im want to count a list of ID numbers on a sheet e.g TW656565A

Only problem is some of them are repeated and I dont want to count the same ID twice

Normally I would use the function =COUNT ( then highlight area) this obviously is not working!!

Could someone please help, I would be very grateful!

 

[Domenic]

Re: Help with which COUNT function to use

Try the following...

=SUMPRODUCT(--(A1:A100<>""),1/COUNTIF(A1:A100,A1:A100&""))

OR

=SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[bigbob]

Re: Help with which COUNT function to use

Hi
If you apply an AUTOFILTER to your list then use one of the SUBTOTAL variations it will give you a count of the unique entries
Bob

 

[jindon]

Re: Help with which COUNT function to use

Hi,

I've created an UDF just while ago which couonts unique values within the range
and it ignores blank cells.

use it like

=Count_Uniq(YourRange)

where YourRange need to be adjusted

Function Count_Uniq(r As Range) As Long 
    Dim a As Variant, x As Variant 
    Dim dic As Object 
    Set dic = CreateObject("scripting.dictionary") 
    a = r.Value 
    For Each x In a 
        If Not IsEmpty(x) And Not dic.exists(x) Then 
            dic.Add x, Nothing 
        End If 
    Next 
    Count_Uniq = dic.Count 
    Set dic = Nothing 
    Erase a 
End Function

hope this helps

jindon

 

[meb]

Re: Help with which COUNT function to use

Try =countdiff(range)