SUMPRODUCT or SUMIF, lookup(9.99999999999999E+307,SEARCH

[srizki]

I am in need of a formula, SUMPRODUCT or SUMIF, that search a word in a text string.
So, for example,

Pipeline

Terminals

Gas Storage

BES

BDL

International

<tbody> </tbody>

Need to be searched in the following and SUM all PIPE LINE amount from let’s say column F.

00001 GREAT PIPE LINE CO. L.P.

00004 EVERLAST PIPE LINE CO. L.P.

00005 CHEVRON PIPE LINE HOLDINGS, L.

00020 BIG RIVER PIPE LINES LLC

00022 EXXON PIPE LINE TRANSPORTATI

00028 BP PARTNERS, L.P.

00031 LAUREN PIPE LINE CO. L.P.

00056 NORWAY PIPE LINE COMPANY, LLC

<tbody> </tbody>

Thanks

 

[Scott Huish]

Assuming your text in Column A.

=SUMIF(A1:A8,"*PIPE LINE*",F1:F8)

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:448px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Arial; ">00001 GREAT PIPE LINE CO. L.P.</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">10</td><td > </td><td > </td><td style="text-align:right; ">275</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Arial; ">00004 EVERLAST PIPE LINE CO.L.P.</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">20</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Arial; ">00005 CHEVRON PIPE LINE HOLDINGS, L.</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">30</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Arial; ">00020 BIG RIVER PIPE LINES LLC</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">40</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-family:Arial; ">00022 EXXON PIPE LINE TRANSPORTATI</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">50</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Arial; ">00028 BP PARTNERS,L.P. </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">55</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Arial; ">00031 LAUREN PIPE LINE CO. L.P.</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">60</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Arial; ">00056 NORWAY PIPE LINE COMPANY, LLC</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">65</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I1</td><td >=SUMIF(A1:A8,"*PIPE LINE*",F1:F8)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[srizki]

Thank you very much.