sum with two conditions

I'd like to sum up the value in column C if A==D and if the title contains "MT" or "LS" then save the sums for example in column E. One field for MT and one for LS. I tried sum-product, sum-ifs,a com开发者_如何转开发bination of isnumber(vlookup(B) nothing has worked so far.

ID(A)   Title(B)   Value(C)  Reference(D)

1   title1_MT   2   1
1   title1_LS   7   
1   title2_MT   4   
1   title2_LS   5   
2   title3_MT   6   2
2   title3_LS   14  
2   title4_MT   23  
2   title4_LS   4   

If you know that the MT or LS will always be the last two characters, you can use this:

=SUMPRODUCT(C2:C9 * (RIGHT(B2:B9,2)="MT") * (A2:A9=D2:D9))

You apparently can't use find() or search() because if the searched-for text does not exist, these functions return errors, and functions like iserror, if and iferror don't work in the array context (noting that all arguments to sumproduct are implicitly treated as arrays).

In column E, this will only display the value if the conditions you outlined are true:

=IF(AND((OR(RIGHT(B2,2)="MT",RIGHT(B2,2)="LS")),A2=D2),C2,0)

Then SUM the results of column E.

Hope this helps.