Sumproduct with UNIQUE records returned?

[doug firr]

I have a sumproduct formula that draws on named ranges like this:

=sumproduct(
(createdate>=b1)*
(createdate<=b2)*
(email))

b1 is 1/1/14 and b2 is 1/31/14 so I'm asking for all records created in January.

But how do I ask for the count of DISTINCT email addresses? It's a pretty large data set ~ 200k records, fyi.

 

[circledchicken]

Hi,

As per Aladin's comment a VBA user defined function may also be faster option.

Here is a sample function you can try and adjust as appropriate (note this is a modified function based on the approach described by Charles Williams here Improving Performance in Excel 2007)

Public Function COUNTU(createdate As Range, _
                       email As Range, _
                       d1 As Long, _
                       d2 As Long) As Variant
    
    Dim colUniques      As New Collection
    Dim vArr(0 To 1)    As Variant
    Dim v               As Variant
    Dim i               As Long
    
    vArr(0) = createdate
    vArr(1) = email
    
    On Error Resume Next
    For i = LBound(vArr(0)) To UBound(vArr(0))
        v = CLng(vArr(0)(i, 1))
        If v >= d1 Then
            If v <= d2 Then
                v = CStr(vArr(1)(i, 1))
                If Len(v) > 0 Then
                     colUniques.Add v, v
                End If
            End If
        End If
    Next i
    
    COUNTU = colUniques.Count


End Function

To use the function:
- Paste it into a regular module (here are instructions on how to do this - http://www.contextures.com/xlvba01.html#Regular)
- Then enter the following in a cell like you would any other function: =COUNTU(createdate, email, B1, B2)

Also, for a SQL alternative you don't necessarily need a third party add-in.
You could write a query with the built in MSQuery - something like this perhaps:

SELECT COUNT(*) AS Result
  FROM (SELECT DISTINCT *
    FROM (SELECT email 
      FROM Table1
        WHERE (Table1.createdate Between {ts '2014-01-01 00:00:00'} And {ts '2014-01-31 00:00:00'})))

The process and associated query editor can be fiddly though - I think the VBA approach might be more straight forward.

If your using Excel 2013 distinct counts are now directly available in PivotTables so that might be the easiest approach.

Finally if you have access to the PowerPivot add-in for Excel 2010 or 2013, then you could also create a DISTINCT count measure that way - here is some detail: Distinct Count in PowerPivot v2

 

[doug firr]

Thanks for all the info. Because I'm familiar with SQL the MSQuery might be an idea but it does seem fiddly. I'd never heard of it till you mentioned it. I can't event seem to "connect" to the tab with all the raw data within this file. I'll keep trying though, cheers for the tips.