Sum Values with different criteria without repeating

[jacquelineBo]

Hello,

I need to sum the amount that appears in the column C every time that on the column B appears "tech" or "error", however there are lines on the column B that appears Tech and Error in the same cell and I dont want to sum the amounts of column C twice. How can I do it, please?

Tech Tech op 2 Error Tech problems 5 tech error 6 hiden 7 open 5 error op 9 tech error with computer 4 error on the table 2 <tbody> </tbody>

<tbody>
</tbody>

 

[AliGW]

Try this:

=(SUMIF(B$1:B$8,"*tech*",C$1:C$8)+SUMIF(B$1:B$8,"*error*",C$1:C$8))-SUMIFS(C$1:C$8,B$1:B$8,"*error*",B$1:B$8,"*tech*")

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​
1​	Tech		Tech op	2​
2​	Error		Tech problems	5​
3​			tech error	6​
4​	28​		hiden	7​
5​			open	5​
6​			error op	9​
7​			tech error with computer	4​
8​			error on the table	2​

<tbody>
</tbody>

In A4 control+shift+enter:

=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(A1:A2),C1:C8))+0,ROW(A1:A2)^0),D1:D8))

If you want to avoid matching tech against technician...

Control+shift+enter:

=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(" "&A1:A2&" ")," "&C1:C8&" "))+0,ROW(A1:A2)^0),D1:D8))