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

#### srizki

##### Well-known Member
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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>

Thank you very much.

1,219,518
Messages
6,148,747
Members
450,832
Latest member
Tyroneb90

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back