Sumproduct return unique values only not working

[doug firr]

This formula is to return the unique values in a range. The range in question is a list of email addresses.

=SUMPRODUCT(1/COUNTIF($P$2:$P$70348,$P$2:$P$70348))

I have used this formula to count unique values before and have not had problems.

It returns 0 when it should be closer to 50 or 60k.

Any ideas?

 

[Andrew Poulsom]

Too many criteria rows to cope with I expect.

 

[doug firr]

Too many criteria rows to cope with I expect.

Andrew - your absolutely right. I left for a meeting and came back 20 minutes later - my 0's had turned to more realistic numbers. Problem solved itself.

 

[Aladin Akyurek]

Andrew - your absolutely right. I left for a meeting and came back 20 minutes later - my 0's had turned to more realistic numbers. Problem solved itself.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($P$2:$P$70348<>"",MATCH($P$2:$P$70348,$P$2:$P$70348,0)),
    ROW($P$2:$P$70348)-ROW($P$2)+1),1))

This should be faster.

 

[Fazza]

Maybe a query table would be preferable? SQL below. Or a pivot table.

SELECT COUNT(*) AS MyCount
FROM
(SELECT DISTINCT field_name
FROM your_data)

 

[T. Valko]

This formula is to return the unique values in a range. The range in question is a list of email addresses.

=SUMPRODUCT(1/COUNTIF($P$2:$P$70348,$P$2:$P$70348))

I have used this formula to count unique values before and have not had problems.

It returns 0 when it should be closer to 50 or 60k.

Any ideas?

Try this array formula**:

=SUM(IF(FREQUENCY(MATCH($P$2:$P$70348,$P$2:$P$70348,0),ROW($P$2:$P$70348)-ROW($P$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Biz]

Options like pivot tables or advance filter with unique value ticked or UDF or vba.
Array formulas over massive range will down slow calculation grounding halt.

 

[T. Valko]

Options like pivot tables or advance filter with unique value ticked or UDF or vba.
Array formulas over massive range will down slow calculation grounding halt.

On my old gutless machine the array formula I suggested takes slightly less than 1 second to calculate. That's not too bad considering what the formula is doing.

If the OP is interested in using a faster UDF...

Public Function COUNTU(theRange As Range) As Variant
'Code by Charles Williams
    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

Then:

=COUNTU($P$2:$P$70348)

This UDF is just about twice as fast to calculate compared to the array formula. On my machine it took less than half a second to calculate.

 

[Fazza]

On my old gutless machine the array formula I suggested takes slightly less than 1 second to calculate. That's not too bad considering what the formula is doing.

If the OP is interested in using a faster UDF...

...

Then:

=COUNTU($P$2:$P$70348)

This UDF is just about twice as fast to calculate compared to the array formula. On my machine it took less than half a second to calculate.

Your gutless old machine is awesome at slightly less than 1 second. My work PC takes about 4 minutes. I guess that is why I don't use array formulas - stopped using them ~10 years ago in fact.

For me the UDF calculates as for you.

 

[Fazza]

FWIW, I recall an old post where array formulas were taking over an hour and a query table solution only a fraction of a second.