Count Unique Records
 

I have the following table (Excel 2007)

Month Broker #
December 32350
December 32350
December 24715
January 24715
January 24715
January 44063

(table is a1:b7)
I want to search for a month and then count the unique records in column B.
So for the month of January, it would return 2 since one of the 3 records is
a duplicate. Any ideas?

Count Unique Records
 

Try this:

=SUM(N(FREQUENCY(IF(A2:A7="January",B2:B7),B2:B7) 0))

ctrl+shift+enter, not just enter


"Jon Dow" wrote:

I have the following table (Excel 2007)

Month Broker #
December 32350
December 32350
December 24715
January 24715
January 24715
January 44063

(table is a1:b7)
I want to search for a month and then count the unique records in column B.
So for the month of January, it would return 2 since one of the 3 records is
a duplicate. Any ideas?

Count Unique Records
 

Try this:

Both formulas need to be array entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

If there will be no empty cells in column B:

D2 = January

=COUNT(1/FREQUENCY(IF(A2:A7=D2,MATCH(B2:B7,B2:B7,0)),ROW(A2 :A7)-MIN(ROW(A2:A7))+1))

If there might be empty cells in column B:

=COUNT(1/FREQUENCY(IF((A2:A7=D2)*(B2:B7<""),MATCH(B2:B7,B2 :B7,0)),ROW(A2:A7)-MIN(ROW(A2:A7))+1))

Biff

"Jon Dow" wrote in message
...
I have the following table (Excel 2007)

Month Broker #
December 32350
December 32350
December 24715
January 24715
January 24715
January 44063

(table is a1:b7)
I want to search for a month and then count the unique records in column
B.
So for the month of January, it would return 2 since one of the 3 records
is
a duplicate. Any ideas?

Count Unique Records
 

Wow that's great. Thanks. This Forum ROCKS!!

"Teethless mama" wrote:

Try this:

=SUM(N(FREQUENCY(IF(A2:A7="January",B2:B7),B2:B7) 0))

ctrl+shift+enter, not just enter


"Jon Dow" wrote:

I have the following table (Excel 2007)

Month Broker #
December 32350
December 32350
December 24715
January 24715
January 24715
January 44063

(table is a1:b7)
I want to search for a month and then count the unique records in column B.
So for the month of January, it would return 2 since one of the 3 records is
a duplicate. Any ideas?

Count Unique Records
 

D2 = January

=COUNT(1/FREQUENCY(IF(A2:A7=D2,B2:B7),B2:B7))

Biff

"Teethless mama" wrote in message
...
Try this:

=SUM(N(FREQUENCY(IF(A2:A7="January",B2:B7),B2:B7) 0))

ctrl+shift+enter, not just enter


"Jon Dow" wrote:

I have the following table (Excel 2007)

Month Broker #
December 32350
December 32350
December 24715
January 24715
January 24715
January 44063

(table is a1:b7)
I want to search for a month and then count the unique records in column
B.
So for the month of January, it would return 2 since one of the 3 records
is
a duplicate. Any ideas?