count unique records in a nested sumproduct formula

[doug firr]

I've been pulling my hair out here. I can get the results via access and using autofilter so I know what number I need to arrive at.

I have a table of database registrants that includes, amongst other things, a date the record was entered and a unique identifier, in this case email address.

I'd like to count the number of UNIQUE registrants in the database that registered between jan 1st and oct 31st 2012.

I have tried something like

=SUMPRODUCT(($D$23:$D$18248>$C$11)*($D$23:$D$18248<M11)*(1/countif($W$23:$W$18248,$W$23:$W$18248))

Where column D is a date stamp and column W is the unique identifier (email address). Cell C11 is 1/1/12 and cell M11 is 11/1/12.

Have also tried another method - I sorted the table alphabetically by column W - identifier, then added a column to the right. Here is an example entry: =IF(W25=W26,"no","yes") where if the record matches the one below it is not unique. I then applied the sumproduct formula as above but rpelaced the 1/countif condition with (X23:X18248="yes").

In both cases I get the wrong number.

Have also tried countifs - no Joy.

There must be a best practice here? Any ideas welcome?

 

[Jugantarji]

Could you do a simple formula to display the information say in column v
If(and(W1> the date,W1< the date),w1,0) and drag it down.
Then with this information you could do an array formula to display only unique records, say in column x
=IF(ISNA(INDEX($V1:$V$1048,MATCH(0,COUNTIF($X$2:X2,$V$1:$v$1048),0))),"",(INDEX($v$1:$v$1048,MATCH(0,COUNTIF($x$2:x2,$v$3:$v$1048),0))))

 

[T. Valko]

I've been pulling my hair out here. I can get the results via access and using autofilter so I know what number I need to arrive at.

I have a table of database registrants that includes, amongst other things, a date the record was entered and a unique identifier, in this case email address.

I'd like to count the number of UNIQUE registrants in the database that registered between jan 1st and oct 31st 2012.

I have tried something like

=SUMPRODUCT(($D$23:$D$18248>$C$11)*($D$23:$D$18248<M11)*(1 countif($W$23:$W$18248,$W$23:$W$18248))

Where column D is a date stamp and column W is the unique identifier (email address). Cell C11 is 1/1/12 and cell M11 is 11/1/12.

Have also tried another method - I sorted the table alphabetically by column W - identifier, then added a column to the right. Here is an example entry: =IF(W25=W26,"no","yes") where if the record matches the one below it is not unique. I then applied the sumproduct formula as above but rpelaced the 1/countif condition with (X23:X18248="yes").

In both cases I get the wrong number.

Have also tried countifs - no Joy.

There must be a best practice here? Any ideas welcome?

Try this array formula**:

=SUM(IF(FREQUENCY(IF($D$23:$D$18248>=$C$11,IF($D$23:$D$18248<$M$11,MATCH($W$23:$W$18248,$W$23:$W$18248,0))),ROW($W$23:$W$18248)-ROW($W$23)+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.

 

[doug firr]

Note for some reason the example formula I cut n paste did not all paste. The full formula is:
sumproduct(($D$25:$D$18250>C13)*($D$25:$D$18250<M13)*(1/countif($W$25:$W$18250,$W$25:$W$18250))

 

[doug firr]

Why is my formula not showing?! One last time: =sumproduct(($D$25:$D$18250>C13)*($D$25:$D$18250<M13)*(1/countif($W$25:$W$18250,$W$25:$W$18250))

 

[doug firr]

Thanks for the pointer Biff but does not seem to be working? I hold ctrl and shift then hit enter. Some curly braces surround the formula but the number returned is 0?

 

[doug firr]

Biff/T Valko, I got this to work - thanks!

How would I add another condition to the formula? I would like to say in addition, include only where column Z(Z23:Z18248)="prospect"

 

[T. Valko]

Biff/T Valko, I got this to work - thanks!

How would I add another condition to the formula? I would like to say in addition, include only where column Z(Z23:Z18248)="prospect"

Like this...

=SUM(IF(FREQUENCY(IF($D$23:$D$18248>=$C$11,IF($D$23:$D$18248<$M$11,IF($Z$23:$Z$18248="prospect",MATCH($W$23:$W$18248,$W$23:$W$18248,0)))),ROW($W$23:$W$18248)-ROW($W$23)+1),1))

Still array entered.

 

[doug firr]

Awesome - thank you very much!

 

[T. Valko]

Awesome - thank you very much!

You're welcome!