count occurances

[kevcar40]

hi
i have a spreedsheet with three columns of data
column CAholds the op number
column CB holds the fault description
column CC holds the downtime
what i want to do is count the amount of downtime for each fault
decription when it occurs against an op number
OP915 SPINDLE FAULT 00:00:25
OP915 SPINDLE FAULT 00:00:13
OP915 SPINDLE FAULT 00:00:02
OP915 SPINDLE FAULT 00:00:05
OP018 LOWER LIFTING 00:01:09
OP018 LOWER LIFTING 00:02:33
OP018 LOWER LIFTING 00:01:37
OP018 LOWER LIFTING 00:03:30
OP018 LOWER LIFTING 00:01:29

result
OP915 SPINDLE FAULT 00:00:45
OP018 LOWER LIFTING 00:10:18


thanks

kevin

 

[PCLIVE]

See if this is what you want.

=SUMPRODUCT(--(CA1:CA100="OP915"),--(CB1:CB100="SPINDLE FAULT"),CC1:CC100)

HTH,
Paul

 

[Mike H]

Try:-

=SUMPRODUCT((A1:A10="OP915")*(B1:B10="Spindle fault")*(C1:C10))

Mike

 

[Toppers]

=SUMPRODUCT(--($CA$1:$CA$9="OP915"),--($CB$1:$CB$9="SPINDLE
FAULT"),($CC$1:$CC$9))

Format cell as hh:mm:ss or [hh]:mm:ss if > 24 hours

It's better if you put the parameters in cells:

=SUMPRODUCT(--($CA$1:$CA$9=X1),--($CB$1:$CB$9=X2),($CC$1:$CC$9))

X1="OP915"
X2="SPINDLE FAULT"

HTH