Hi,
I have a unique count using a sumproduct formula like this...

=sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="")

which seems to work ok. The problem is I then want to add another criteria
saying only count uniques where the date in column F = "01/01/05". Nothing
I've tried seems to work.

Thanks in advance!

Hi Naomi,
When you reference dates in formulas like this, the syntax is:

F2:F100=DATEVALUE("5/5/2003")

HTH

"Naomi" wrote:

Hi,
I have a unique count using a sumproduct formula like this...

=sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="")

which seems to work ok. The problem is I then want to add another criteria
saying only count uniques where the date in column F = "01/01/05". Nothing
I've tried seems to work.

Thanks in advance!

D F

Client 1 Jan-04
Client 2 Feb-04
Client 1 Jan-04
Client 2 Jan-04
Client 4 Jan-04

I would hope for this to give me a value of 3 but instead I get a value of
2.5?!
ie. Unique clients that also have Jan-04 in col F.
Thanks.

"Ken" wrote:

It's hard for me to tell what's going on without seeing sample data. Perhaps
you could list a sample of 9-10 rows from columns D and F and tell us your
expected results?

"Naomi" wrote:

Thanks Ken,
I've changed it to...

=sumproduct((F2:F100=DATEVALUE("1/1/2004")/(countif(D2:D100,D2:D100)+(D2:D100=""))

but I am getting a result that is a few short of what it should be. Any
ideas? I'm going crazy here!!

Thanks.


"Ken" wrote:

Hi Naomi,
When you reference dates in formulas like this, the syntax is:

F2:F100=DATEVALUE("5/5/2003")

HTH

"Naomi" wrote:

Hi,
I have a unique count using a sumproduct formula like this...

=sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="")

which seems to work ok. The problem is I then want to add another criteria
saying only count uniques where the date in column F = "01/01/05". Nothing
I've tried seems to work.

Thanks in advance!

Thanks Ken,
I've changed it to...

=sumproduct((F2:F100=DATEVALUE("1/1/2004")/(countif(D2:D100,D2:D100)+(D2:D100=""))

but I am getting a result that is a few short of what it should be. Any
ideas? I'm going crazy here!!

Thanks.


"Ken" wrote:

Hi Naomi,
When you reference dates in formulas like this, the syntax is:

F2:F100=DATEVALUE("5/5/2003")

HTH

"Naomi" wrote:

Hi,
I have a unique count using a sumproduct formula like this...

=sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="")

which seems to work ok. The problem is I then want to add another criteria
saying only count uniques where the date in column F = "01/01/05". Nothing
I've tried seems to work.

Thanks in advance!

It's hard for me to tell what's going on without seeing sample data. Perhaps
you could list a sample of 9-10 rows from columns D and F and tell us your
expected results?

"Naomi" wrote:

Thanks Ken,
I've changed it to...

=sumproduct((F2:F100=DATEVALUE("1/1/2004")/(countif(D2:D100,D2:D100)+(D2:D100=""))

but I am getting a result that is a few short of what it should be. Any
ideas? I'm going crazy here!!

Thanks.


"Ken" wrote:

Hi Naomi,
When you reference dates in formulas like this, the syntax is:

F2:F100=DATEVALUE("5/5/2003")

HTH

"Naomi" wrote:

Hi,
I have a unique count using a sumproduct formula like this...

=sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="")

which seems to work ok. The problem is I then want to add another criteria
saying only count uniques where the date in column F = "01/01/05". Nothing
I've tried seems to work.

Thanks in advance!

Try the following...

=SUM(IF(FREQUENCY(IF((A1:A5<"")*(B1:B5="1/1/2004"+0),MATCH(A1:A5,A1:A5,0
)),ROW(INDIRECT("1:"&ROWS(A1:A5))))0,1,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Naomi" wrote:

D F

Client 1 Jan-04
Client 2 Feb-04
Client 1 Jan-04
Client 2 Jan-04
Client 4 Jan-04

I would hope for this to give me a value of 3 but instead I get a value of
2.5?!
ie. Unique clients that also have Jan-04 in col F.
Thanks.