Sumproduct multiple criteria

[EvaCheng0404]

I want to use sumproduct with multiple criteria.
I have a cell A1 to enter a text e.g. aaa or any text.
How can I use mid function to find cells that refer to A1 in sumproduct formula.

Thanks
Eva

 

[EvaCheng0404]

Thanks
How about if cell P5 ：AW

I want to search part of the text in J column

 

[hagia_sofia]

That should not be a problem, just test it; the only question is whether your match is supposed to be case-sensitive (FIND function) or case-insensitive (SEARCH function)