DCOUNT Unique Values

[Wilba]

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!

 

[Bob Phillips]

=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]

=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!