Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
Count unique records... with a twist... Need help! | Excel Worksheet Functions | |||
Count the number of unique records | Excel Worksheet Functions |