Sum If Cell Contains "this word", but not "this word"

[south0085]

I have a list of part types in column E1:E57. In cell F59, I want to add up all the ones that include the word "Cover", but I don't want it to include any that contain the word "service". Like this:

Sheet1

	E	F
Mitsubishi Service Cover - ZC
989A COVER
B12P Cover
Toyota Service - 989A Cover
Toyota Service Cover

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:190px;"><col style="width:70px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"] 28 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"] 56 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"] 87 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"] 100 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"] 500 [/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



So...the formula return a total of: 143

Thank you for your help.

 

[VBA Geek]

Excel 2013/2016
	A	B	C	D	E
1	Mitsubishi Service Cover - ZC	28
2	989A COVER	56
3	B12P Cover	87
4	Toyota Service - 989A Cover	100			143
5	Toyota Service Cover	500
6
Sheet1

Cell Formulas
Range	Formula
E4	=SUMIFS(B1:B5,A1:A5,"*cover*",A1:A5,"<>*service*")

 

[south0085]

Excel 2013/2016

	A	B	C	D	E
Mitsubishi Service Cover - ZC
989A COVER
B12P Cover
Toyota Service - 989A Cover
Toyota Service Cover

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]56[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]143[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=SUMIFS(B1:B5,A1:A5,"*cover*",A1:A5,"<>*service*")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you much! The <> is the part I couldn't figure.