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?



[TABLE="width: 285"]
<tbody>[TR]
[TD="align: left"][TABLE="width: 349"]
<tbody>[TR]
[TD="align: left"]Tech[/TD]
[TD][/TD]
[TD="align: left"]Tech op[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: left"]Error[/TD]
[TD][/TD]
[TD="align: left"]Tech problems[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]tech error[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]hiden[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]open[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]error op[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]tech error with computer[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]error on the table[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Tech[/TD]
[TD][/TD]
[TD]Tech op[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Error[/TD]
[TD][/TD]
[TD]Tech problems[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]tech error[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

28​

[/TD]
[TD][/TD]
[TD]hiden[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]open[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]error op[/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]tech error with computer[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]error on the table[/TD]
[TD]

2​

[/TD]
[/TR]
</tbody>[/TABLE]


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))