# Re: Help with which COUNT function to use

#### kullie009

##### New Member
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!!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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!

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

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
Code:
=Count_Uniq(YourRange)
where YourRange need to be adjusted

Code:
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
End If
Next
Count_Uniq = dic.Count
Set dic = Nothing
Erase a
End Function

hope this helps

jindon

Re: Help with which COUNT function to use

Try =countdiff(range)

Replies
4
Views
243
Replies
1
Views
270
Replies
4
Views
573
Replies
2
Views
439
Replies
1
Views
352

1,214,323
Messages
6,118,890
Members
448,854
Latest member
Eduard_Stoo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back