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.

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

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

--
HTH

Bob Phillips

"Neville" wrote in message
...
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, you are a genius! Thanks very much.

Neville

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" schreef in bericht
...
=SUM(IF(FREQUENCY(IF((B2:B20="John"),A2:A20),IF((B 2:B20="John"),A2:A20))0,1
,0))

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

--
HTH

Bob Phillips

"Neville" wrote in message
...
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.

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.

--
HTH

Bob Phillips

"Jack Sons" wrote in message
...
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" schreef in bericht
...

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

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

--
HTH

Bob Phillips

"Neville" wrote in message
...
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.

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

"Neville" wrote:

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.

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.


On Sun, 18 Sep 2005 11:44:10 -0700, "John Moore"
wrote:

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

"Neville" wrote:

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.