DCOUNT Unique Values
 

Hello. Apologies if this is an old problem.

I'm using DCOUNT to count a subset of records in a table, and it's doing
exactly what I want, but now I want to count only the records with a unique
value in one of the columns. Like -

A B C D
1 Date Account
2 01-Oct 123
3 02-Oct 456
4 03-Oct 123
5 03-Oct 456
6 04-Oct 789
7
8 Date Date Account
9 =02-Oct <=03-Oct =DCOUNT(A1:B6,,A8:C9)

I want to get D9 to show me 2 instead of 3. I assume there is nothing I can
put in C9 to make DCOUNT work that way, so I expect if it's possible it
would have to be done differently. That's fine as long as that will give me
the unique count for a subset of the data (as defined for DCOUNT by the text
in A9 and B9).

Any ideas? Thanks!

DCOUNT Unique Values
 

=COUNT(1/FREQUENCY(IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6),IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Wilba" wrote in message
...
Hello. Apologies if this is an old problem.

I'm using DCOUNT to count a subset of records in a table, and it's doing
exactly what I want, but now I want to count only the records with a
unique value in one of the columns. Like -

A B C D
1 Date Account
2 01-Oct 123
3 02-Oct 456
4 03-Oct 123
5 03-Oct 456
6 04-Oct 789
7
8 Date Date Account
9 =02-Oct <=03-Oct =DCOUNT(A1:B6,,A8:C9)

I want to get D9 to show me 2 instead of 3. I assume there is nothing I
can put in C9 to make DCOUNT work that way, so I expect if it's possible
it would have to be done differently. That's fine as long as that will
give me the unique count for a subset of the data (as defined for DCOUNT
by the text in A9 and B9).

Any ideas? Thanks!

DCOUNT Unique Values
 

Bob Phillips wrote:

=COUNT(1/FREQUENCY(
IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6),
IF((A2:A6=--"2007-10-02")*(A2:A6<=--"2007-10-03"),B2:B6)))

which is an array formula ...

Magic! Thanks a bunch.

Would you mind talking me through this a little?

Let me see what I can understand. You're comparing the value in each A cell
with the two reference dates.

The "--" looks like a double negation - what is it doing?

Does the "*" operator give you a logical "and"?

If the dates are within range you take the values from the corresponding B
cells and give them as both parameters in FREQUENCY. I don't understand what
FREQUENCY gives you when data = bins.

Then you're taking the reciprocals and counting them.

I think I'll be able to understand this if I know what "--" is doing, but
more importantly what FREQUENCY does when data = bins.

Thanks! :-)


Wilba wrote:

I'm using DCOUNT to count a subset of records in a table, and it's doing
exactly what I want, but now I want to count only the records with a
unique value in one of the columns. Like -

A B C D
1 Date Account
2 01-Oct 123
3 02-Oct 456
4 03-Oct 123
5 03-Oct 456
6 04-Oct 789
7
8 Date Date Account
9 =02-Oct <=03-Oct =DCOUNT(A1:B6,,A8:C9)

I want to get D9 to show me 2 instead of 3. I assume there is nothing I
can put in C9 to make DCOUNT work that way, so I expect if it's possible
it would have to be done differently. That's fine as long as that will
give me the unique count for a subset of the data (as defined for DCOUNT
by the text in A9 and B9).

Any ideas? Thanks!