Re: Help with which COUNT function to use

kullie009

New Member
Joined
Jan 21, 2005
Messages
1
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 31, 2002
Messages
82
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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 
            dic.Add x, Nothing 
        End If 
    Next 
    Count_Uniq = dic.Count 
    Set dic = Nothing 
    Erase a 
End Function

hope this helps

jindon
 

meb

New Member
Joined
May 13, 2003
Messages
31
Re: Help with which COUNT function to use

Try =countdiff(range)
 

Forum statistics

Threads
1,147,846
Messages
5,743,515
Members
423,801
Latest member
paulj4177

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top