Sumproduct to sum multiple column of data based on multiple criteria on different columns

[AndewY]

Hi,
I'm trying to sum numbers on a set of columns based on corresponding cell contents on different columns.
One type of criteria is that the cell does not contain certain text.

I have work will this.

=SUMPRODUCT((C2:C3000=O11)*(Z2:BU3000)
*(ISNUMBER(SEARCH({"*OT*","*TOIL*"},L2:L3000))))

Where C2:C3000 is column containing names
Z2:BU3000 are the multiple columns of numbers that need to be summed
and L2:L3000 is column containing comments.

I hope that my explanation above doesn't confuse and thanks in advance!

 

[AndewY]

Sorry for the multiple posts.
I'm getting #N/A as a result.
Have tried using {SUM(IF()} but then couldn't get the formula to excluded where cells contained certain text e.g. <>"*OT*" returned an error.

Thanks again