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!