Wilba wrote:
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?

OK, I'll answer my own question. :-) It looks like the "--" is doing the
equivalent of DATEVALUE. In the real thing I'm comparing dates directly so I
don't need it anyway. But I can't find any documentation on the "--" ... any
clues?

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

It's just multiplying 1s and 0s, so in effect, yes.

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.

I think I understand this now. You get an array of the number of occurences
of each unique value in the B cells.

Then you're taking the reciprocals and counting them.

Which seems like a perverse way to do it. I prefer to use
SUM(IF(FREQUENCY(...)0,1)), not because it works any better, but because I
can come back in a month and understand what I'm doing.

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

I think I do now.

Thanks! :-)

Thanks me! :-D


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!