| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count occurances
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count occurances
See if this is what you want.
=SUMPRODUCT(--(CA1:CA100="OP915"),--(CB1:CB100="SPINDLE FAULT"),CC1:CC100) HTH, Paul "kevcar40" wrote in message ps.com... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count occurances
Try:-
=SUMPRODUCT((A1:A10="OP915")*(B1:B10="Spindle fault")*(C1:C10)) Mike "kevcar40" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count occurances
=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 "kevcar40" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count consecutive occurances | Excel Worksheet Functions | |||
| Need to count occurances in different columns | Excel Worksheet Functions | |||
| Count # of unique occurances | Excel Worksheet Functions | |||
| count matching occurances | Excel Worksheet Functions | |||
| Count occurances Problem | Excel Worksheet Functions | |||
Linear Mode
