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

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

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

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

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