Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique
This is what I have:
A (Date) B (Emp #) C (Product #) 10-Oct-08 2 700 10-Oct-08 3 710 11-Oct-08 3 700 12-Oct-08 1 702 12-Oct-08 1 700 12-Oct-08 2 700 12-Oct-08 2 700 12-Oct-08 2 740 I want a formula to count how many employees worked on a certain product in a certain day . Example: on 12-Oct-08,2 employees worked on product # 700.Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique
=sumproduct(--(c2:c50="700")*(a2:a50=date(2008,10,12)))
hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "MAANI" escreveu: This is what I have: A (Date) B (Emp #) C (Product #) 10-Oct-08 2 700 10-Oct-08 3 710 11-Oct-08 3 700 12-Oct-08 1 702 12-Oct-08 1 700 12-Oct-08 2 700 12-Oct-08 2 700 12-Oct-08 2 740 I want a formula to count how many employees worked on a certain product in a certain day . Example: on 12-Oct-08,2 employees worked on product # 700.Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique
Try this:
Data in A1:C8 Criteria D1: holds date E1: holds product # =SUM(N(FREQUENCY(IF((A1:A8=D1)*(C1:C8=E1),MATCH(B1 :B8,B1:B8,)),MATCH(B1:B8,B1:B8,))0)) ctrl+shift+enter, not just enter "MAANI" wrote: This is what I have: A (Date) B (Emp #) C (Product #) 10-Oct-08 2 700 10-Oct-08 3 710 11-Oct-08 3 700 12-Oct-08 1 702 12-Oct-08 1 700 12-Oct-08 2 700 12-Oct-08 2 700 12-Oct-08 2 740 I want a formula to count how many employees worked on a certain product in a certain day . Example: on 12-Oct-08,2 employees worked on product # 700.Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique
Try this array formula**:
Named ranges: Date: refers to A1:A8 Emp: refers to B1:B8 Prod: refers to C1:C8 E1 = lookup date = 10/12/2008 F1 = prod number = 700 =COUNT(1/FREQUENCY(IF((Date=E1)*(Prod=F1),Emp),Emp)) Assumes there are no empty cells with Emp Also assumes that your employee numbers are really *numbers* as your sample demonstrates. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "MAANI" wrote in message ... This is what I have: A (Date) B (Emp #) C (Product #) 10-Oct-08 2 700 10-Oct-08 3 710 11-Oct-08 3 700 12-Oct-08 1 702 12-Oct-08 1 700 12-Oct-08 2 700 12-Oct-08 2 700 12-Oct-08 2 740 I want a formula to count how many employees worked on a certain product in a certain day . Example: on 12-Oct-08,2 employees worked on product # 700.Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique
Teethless,I'm applying your function in a workbook,my source is in different
workbook,and my source has 65536 rows,when I applied your function it returns #N/A also I can't save the file !!! "Teethless mama" wrote: Try this: Data in A1:C8 Criteria D1: holds date E1: holds product # =SUM(N(FREQUENCY(IF((A1:A8=D1)*(C1:C8=E1),MATCH(B1 :B8,B1:B8,)),MATCH(B1:B8,B1:B8,))0)) ctrl+shift+enter, not just enter "MAANI" wrote: This is what I have: A (Date) B (Emp #) C (Product #) 10-Oct-08 2 700 10-Oct-08 3 710 11-Oct-08 3 700 12-Oct-08 1 702 12-Oct-08 1 700 12-Oct-08 2 700 12-Oct-08 2 700 12-Oct-08 2 740 I want a formula to count how many employees worked on a certain product in a certain day . Example: on 12-Oct-08,2 employees worked on product # 700.Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
Count Unique Values | Excel Worksheet Functions | |||
Count unique entries... | Excel Worksheet Functions | |||
Unique entry count | Excel Discussion (Misc queries) | |||
Count Unique Entries | Excel Worksheet Functions |