| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values
I have a series of names in column A and corresponding dates in column B. I need to find the number of unique dates (in serial form) corresponding to a name. Col A Col B Jones 38822 Smith 38822 Walker 38822 Jones 38822 Jones 38823 Jones 38823 In cell C1 I need to have the days associated with Jones, in this case, 2. -- JK57 ------------------------------------------------------------------------ JK57's Profile: http://www.excelforum.com/member.php...o&userid=33430 View this thread: http://www.excelforum.com/showthread...hreadid=559010 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting unique values
Try...
=COUNT(1/FREQUENCY(IF(A1:A6="Jones",IF(B1:B6<"",B1:B6)),IF (A1:A6="Jones" ,IF(B1:B6<"",B1:B6)))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , JK57 wrote: I have a series of names in column A and corresponding dates in column B. I need to find the number of unique dates (in serial form) corresponding to a name. Col A Col B Jones 38822 Smith 38822 Walker 38822 Jones 38822 Jones 38823 Jones 38823 In cell C1 I need to have the days associated with Jones, in this case, 2. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting unique values
Hi Jk,
=sumproduct(--(a2:a100="Jones")*(b2:b100=38822)) hth regards from Brazil Marcelo "JK57" escreveu: I have a series of names in column A and corresponding dates in column B. I need to find the number of unique dates (in serial form) corresponding to a name. Col A Col B Jones 38822 Smith 38822 Walker 38822 Jones 38822 Jones 38823 Jones 38823 In cell C1 I need to have the days associated with Jones, in this case, 2. -- JK57 ------------------------------------------------------------------------ JK57's Profile: http://www.excelforum.com/member.php...o&userid=33430 View this thread: http://www.excelforum.com/showthread...hreadid=559010 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting unique values
Domenic Wrote: Try... =COUNT(1/FREQUENCY(IF(A1:A6="Jones",IF(B1:B6<"",B1:B6)),IF (A1:A6="Jones" ,IF(B1:B6<"",B1:B6)))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , JK57 wrote: I have a series of names in column A and corresponding dates in column B. I need to find the number of unique dates (in serial form) corresponding to a name. Col A Col B Jones 38822 Smith 38822 Walker 38822 Jones 38822 Jones 38823 Jones 38823 In cell C1 I need to have the days associated with Jones, in this case, 2. That worked, thanks. -- JK57 ------------------------------------------------------------------------ JK57's Profile: http://www.excelforum.com/member.php...o&userid=33430 View this thread: http://www.excelforum.com/showthread...hreadid=559010 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting unique values with criteria | Excel Worksheet Functions | |||
| list unique values in a column | Excel Worksheet Functions | |||
| Populate combo box with unique values only | Excel Discussion (Misc queries) | |||
| Counting Values | Excel Worksheet Functions | |||
| Counting unique values + frequency | Excel Worksheet Functions | |||
Linear Mode
