Double unique formula

[LiAD]

Hi,

I have a list of machines driven by certain operators in order to make
certain products. As there are 3 shifts per day there are three columns for
operator names per day plus xx rows as there can be any number of products
per day. The products made are only recorded in one column rather than
repeated per operator. I would like to know how many machines each one has
driven on any one day. Example table below

Machine-----Product-----Operator-----Operator-----Operator

A 1 Fred
Dave
A 2 Fred Bill
B 9 Will Mike
Dave
C 5 Steve JB
Paul
C 6 Steve JB
C 7 Steve
Paul
D 12 Fred
Dave

So against each operators name I would like to know how many machines they
used during the day. Results would be

Fred 2
Bill 1
Dave 3
Will 1
Mike 1
Steve 1
JB 1

Counting how many times the name appears does not work as one operator may
make several products during the same shift on one machine – but he has only
driven one machine.

Is it possible to have a formula that can do this operation?

Thanks
LiAD

 

[Jacob Skaria]

--Try the below formula with the data arranged in A1:E8

--Try and replace "Fred" with a cell reference to suit your requirement

--Please note that this is an array formula. You create array formulas in
the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=SUM(N(FREQUENCY(IF(($C$2:$E$8="Fred"),MATCH($A$2:$A$8,$A$2:$A$8,)),
MATCH($A$2:$A$8,$A$2:$A$8,))>0))

If this post helps click Yes

 

[Bernard Liengme]

I put your data in A1:E8; row 1 being labels
Then I made this table starting in G1
Op A B C D Machines
Fred 2 0 0 1 2
Bill 1 0 0 0 1
Dave 1 1 0 1 3
Will 0 1 0 0 1
Mike 0 1 0 0 1
Steve 0 0 2 1 2
JB 0 0 2 0 1
Paul 0 0 1 1 2

The formula in H2 (next to Fred, under A) is
=SUMPRODUCT(($C$2:$E$8=$G2)*($A$2:$A$8=H$1))
This tells how many tiles Fred worked on A
The formula is copied down to Paul and across to machine D
In L2 (under MachinesI I have =COUNTIF(H2:K2,">0")
This is copied down the column
The results are what you asked for: how may different machines each operator
used.

If you want to avoid the table, next to Fred's name in H2 enter
=(SUMPRODUCT(($C$2:$E$8=$G2)*($A$2:$A$8="A"))>0)+(SUMPRODUCT(($C$2:$E$8=$G2)*($A$2:$A$8="B"))>0)+(SUMPRODUCT(($C$2:$E$8=$G2)*($A$2:$A$8="C"))>0)+(SUMPRODUCT(($C$2:$E$8=$G2)*($A$2:$A$8="D"))>0)
Copy down the column
best wishes

 

[T. Valko]

Try this...

Data in the range A2:E8.

List of unique names in the range G2:Gn

Enter this array formula** in H2 and copy down as needed:

=SUM(IF(FREQUENCY(IF(C$2:E$8=G2,MATCH(A$2:A$8,A$2:A$8,0)),ROW(A$2:A$8)-ROW(A$2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Assume no empty cells in A2:A8.

 

[ryguy7272]

Good one Jacob!
Dave = 2


Ryan---

 

[LiAD]

Works a treat.

thanks a lot

--Try the below formula with the data arranged in A1:E8

--Try and replace "Fred" with a cell reference to suit your requirement

--Please note that this is an array formula. You create array formulas in
the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=SUM(N(FREQUENCY(IF(($C$2:$E$8="Fred"),MATCH($A$2:$A$8,$A$2:$A$8,)),
MATCH($A$2:$A$8,$A$2:$A$8,))>0))

If this post helps click Yes