Double unique formula
Works a treat.
thanks a lot
"Jacob Skaria" wrote:
--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
---------------
Jacob Skaria
"LiAD" wrote:
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
|