Count or Dcount

[Guest]

I have the following database table in Excel:

A B
1 1 Sep John
2 1 Sep Fred
3 1 Sep John
4 2 Sep John
5 3 Sep Fred
6 3 Sep John

How can I count the number of unique dates in which John applies? The answer
should be 3, i.e. 1 Sep, 2 Sep and 3 Sep.

 

[Bob Phillips]

=SUM(IF(FREQUENCY(IF((B2:B20="John"),A2:A20),IF((B2:B20="John"),A2:A20))>0,1
,0))

this is an array formula, so commit with Ctrl-Shift-Enter.

 

[Guest]

A simple way is to do a Countif
=Countif(B1:B6,"John")

 

[Guest]

Bob, you are a genius! Thanks very much.

Neville

 

[Myrna Larson]

I was about to say the same thing, but, since Bob usually knows what he's
doing, I double-checked the original message, which specifies UNIQUE DATES.
COUNTIF will give a result of 4, since there are 2 entries for 1 Sep, and the
OP says he wants a result of 3.

Score 1 for Bob, 0 for Myrna and John <g>.

 

[Jack Sons]

Bob,

I put the data in A2:B7.
With key F9 I see that
FREQUENCY(IF((B2:B7="John"),A2:A7),IF((B2:B7="John"),A2:A7))
results in {2\0\1\1\0}
but I don't understand why. Please enlighten me.

Jack Sons
The Netherlands

 

[Bob Phillips]

Hi Jack,

What it is doing in essence is taking every item in A2:A7 in turn, and
checking how often that item occurs in A2:A&, but only if the corresponding
cell in B also equals John. So, where you see 2, that is the 3rd Sep for
John, the two occurrences of 1 relate to the 1st and 2nd Sep entries for
John, and the tow 0 occurrences relates to the Fred entries. The frequencies
are checked for greater than 0, and summed as 1 for those instances.