If(B2 (text is not in a list),0:0:0, (Time from column A))

[crouska]

[FONT=&quot]I’m working on a project where I’m tracking machine down time. I get an export from a system with specific faults. Some faults I want to include in a time sum and others I do not. I’m looking at VLookUp functions and arrays that I think could do the job. My downloaded data looks like this:[/FONT]

ColumnA ColumnB ColumnC ColumnD<table class="MsoNormalTable" style="width: 432.8pt; margin-left: 4.6pt; border-collapse: collapse;" width="577" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 15pt;"><td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103">1h 20m 35s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABInserterAlarm
</td> <td style="width: 2.25in; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="216"> Autobagger 02-Insert 5 Alert
</td> <td colspan="2" style="width: 76.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="102">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 1h 01m 20s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABInserterAlarm
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-Insert 4 Alert
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 4s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABInserterAlarm
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-Insert 3 Alert
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 13m 47s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABInserterAlarm
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-Insert 2 Alert
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 5m 05s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABInserterAlarm
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-Insert 1 Alert
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 26m 07s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABMailerFeedError
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-Mailer Feeder Error
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:26:07​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 8m 53s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABMailerFeedError
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-Mailer Load Conv Low
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 1m 46s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABPacketLoadFault
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-Load Pack Home Error
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

1:0:46​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 26m 51s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABPacketLoadFault
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-No Vacuum Error
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:26:51​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 31m 49s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABProductLoadFault
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-No Vacuum Error
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:31:49​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 23m 56s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABProductLoadFault
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-Mailer Load Jam
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:23:56​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 13m 27s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> ABSealerFault
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02-Sealer Induct Package Jam
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:13:27​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 2m 21s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> AutoBaggerClear
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02 Clear Product Function
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 15h 58m 09s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> MasterEStopActivated
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02 - E-Stop
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 0s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> PlcRestart
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02 PLC Restart
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 1d 01m 26s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> PrinterOnLine
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02 Label Printer
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 1d 01m 26s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> PrinterOnLine
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Autobagger 02 Invoice Printer
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 77.3pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103"> 1d 01m 26s
</td> <td style="width: 117pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156"> SystemAuto
</td> <td colspan="2" style="width: 207pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="276"> Augobagger 02 - Run
</td> <td style="width: 31.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="42">

0:0:0​

</td> </tr> <tr height="0"> <td style="border: medium none;" width="99">
</td> <td style="border: medium none;" width="156">
</td> <td style="border: medium none;" width="208">
</td> <td style="border: medium none;" width="56">
</td> <td style="border: medium none;" width="57">
</td> </tr> </tbody></table>
I would want to exclude:
<table class="MsoNormalTable" style="width: 601pt; margin-left: 4.6pt; border-collapse: collapse;" width="801" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;"> <td style="width: 601pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="801"> Autobagger 02-Insert 5 Alert
</td> </tr> <tr style="height: 15pt;"> <td style="width: 601pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="801"> Autobagger 02-Insert 4 Alert
</td> </tr> <tr style="height: 15pt;"> <td style="width: 601pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="801"> Autobagger 02-Insert 3 Alert
</td> </tr> <tr style="height: 15pt;"> <td style="width: 601pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="801"> Autobagger 02-Insert 2 Alert
</td> </tr> <tr style="height: 15pt;"> <td style="width: 601pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="801"> Autobagger 02-Insert 1 Alert
</td> </tr> </tbody></table> Autobagger 02-Mailer Load Conv Low
<table class="MsoNormalTable" style="width: 601pt; margin-left: 4.6pt; border-collapse: collapse;" width="801" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;"> <td style="width: 601pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="801"> Autobagger 02 Clear Product Function
Autobagger 02 Label Printer
</td> </tr> <tr style="height: 15pt;"> <td style="width: 601pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="801"> Autobagger 02 - E-Stop
</td> </tr> </tbody></table> Augobagger 02 – Run
I have a formula that will convert time in the format listed above to an actual excel time so don’t worry about that. Is there a way to do a formula that will place a time in column D based on the time in column A and the fault in column C not matching anything in the 'exclude' list(desired result shown in column D)?

Or even better, create a formula for a grouping summary for machine 1 and then different one for machine 2 without having to copy and paste formulas in column D.

Thanks in advance for all your help!!!
KC