Countif Unique values only?

[Mudbutt]

I'm using Excel 2008. I have a production order # column that has duplicates in it. I also have a week num column with multiple weeks. I need to do a count on unique production order #'s by Week. I'm not sure how to do that.

I found this formula =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1)) which would help me get the count for all the orders, but it doesn't get it to me by week. Can anyone help me add that criteria?

 

[T. Valko]

The COUNTU UDF from Charles Williams is very fast with large datasets too.

The COUNTDIFF( ) function in the Morefunc add-in from Laurent Longre is by far the fastest count uniques function that I know of.

In Excel versions 2003 and earlier it'll count an entire column minus 1 row (65535 total rows) without a blip!

In Excel 2007 and later it'll count an entire column minus 1 row (1048575 total rows) in less than 2 secs.

Of course, these are straight counts so when you start adding conditions then the calc times naturally increase dramatically.

 

[circledchicken]

Ah cool, thanks! I have the add-in but I don't think I have used that function before.

The Morefunc help does say its written as a C++ XLL add-in, so I imagine most of the functions would be fast compared to VBA alternatives - although I don't really know anything about C++ or how to write an XLL add-in?!

 

[Aladin Akyurek]

Ah cool, thanks! I have the add-in but I don't think I have used that function before.

The Morefunc help does say its written as a C++ XLL add-in, so I imagine most of the functions would be fast compared to VBA alternatives - although I don't really know anything about C++ or how to write an XLL add-in?!

http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

 

[circledchicken]

http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

Thanks Aladin - I can see COUNTDIFF is significantly faster than the alternatives.

Do you know what the result is for the COUNTU UDF on the same dataset. I understand as per T. Valko's post that it is probably slower than COUNTDIFF but it might be interesting to see the difference.

 

[T. Valko]

Do you know what the result is for the COUNTU UDF on the same dataset. I understand as per T. Valko's post that it is probably slower than COUNTDIFF but it might be interesting to see the difference.

If you're interested, Charles Williams has posted some calculation timer code here.

You can do the tests and see for yourself.

 

[circledchicken]

If you're interested, Charles Williams has posted some calculation timer code here.

Thanks again - I have skimmed through that before, just been too lazy to actually do the testing! Hopefuly I will get to it one day.

 

[T. Valko]

Thanks again - I have skimmed through that before, just been too lazy to actually do the testing! Hopefuly I will get to it one day.

I do this type of testing quite often.

To make it "easy" I have added to the code (although I'm not much of programmer) and have attached it to a button on one of my toolbars so that it's just a click away.

I select a cell or range of cells with a formula and click the button.

 

[Aladin Akyurek]

Thanks Aladin - I can see COUNTDIFF is significantly faster than the alternatives.

Do you know what the result is for the COUNTU UDF on the same dataset. I understand as per T. Valko's post that it is probably slower than COUNTDIFF but it might be interesting to see the difference.

If COUNTU is not coded in C or C++, the chances are that COUNTDIFF would be faster.

 

[circledchicken]

I do this type of testing quite often.

To make it "easy" I have added to the code (although I'm not much of programmer) and have attached it to a button on one of my toolbars so that it's just a click away.

I select a cell or range of cells with a formula and click the button.

Sounds like a good idea - I think I will do that too!

If COUNTU is not coded in C or C++, the chances are that COUNTDIFF would be faster.

Yes, COUNTU is just a regular UDF written in VBA by Charles Williams (in the same article that T. Valko referenced). I think it was written as a proof-of-concept for a well designed UDF, showing how much faster they can be than regular worksheet formula solutions in certain cases.

 

[T. Valko]

If COUNTU is not coded in C or C++, the chances are that COUNTDIFF would be faster.

COUNTU is written in VBA.

Charles Williams COUNTU UDF

Public Function COUNTU(theRange As Range) As Variant
    Dim colUniques As New Collection
    Dim vArr As Variant
    Dim vCell As Variant
    Dim vLcell As Variant
    Dim oRng As Range
    Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
    vArr = oRng
    On Error Resume Next
    For Each vCell In vArr
    If vCell <> vLcell Then
        If Len(CStr(vCell)) > 0 Then
             colUniques.Add vCell, CStr(vCell)
        End If
    End If
    vLcell = vCell
    Next vCell
    
    COUNTU = colUniques.Count
End Function