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.

 

[doug firr]

Just to add. I found the following formula on here but it's returning a #N/A error. I used CSE and see the curly braces around the formula:

SUM(IF(FREQUENCY(
IF(createdate>=B$1,
IF(createdate<=B$2,
IF(createdate<b$2,
MATCH(email,0))),ROW(email)-ROW(DataBase!$B$3)+1),1))

Database! tab is where the actual data of the ranges are.
There are no blank or nonsense or #N/A records in email or created date. Those ranges are also the exact same length.

Any ideas?</b$2,

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(createdate>=B$1,IF(createdate<=B$2,IF(email<>"",MATCH("~"&email,email&"",0)))),ROW(email)-MIN(ROW(email))+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[doug firr]

@Domenic it's been running for the past 15 minutes - is that expected? I notice that the curly braces are not added yet, perhaps those get added once it's run?

 

[Aladin Akyurek]

What does...

=COUNTA(email)

yield?

 

[doug firr]

@Aladin typing that exactly gives 197493

 

[Aladin Akyurek]

@Aladin typing that exactly gives 197493

That is a lot of cells to process, which will make the frequency formula choke.

You might want to turn to SQL or VBA... Here is a link:

Select and count distinct values in Excel

You can also find a few related links on this board.

 

[doug firr]

The frequency formula just finished running. Took about 45 minutes when left completely alone. Have you used this SQL*XL before? You recommend?

 

[Aladin Akyurek]

The frequency formula just finished running. Took about 45 minutes when left completely alone. Have you used this SQL*XL before? You recommend?

I think SQL does such a count almost immediately. Could you run the recipe with a copy of your file and report back?

 

[doug firr]

I will yes I'm curious to try it