Your formula will work if you add double unary operators. You can
google this to find out more, but essentially it causes the sumproduct
formula to compare each item in the array (in your case,
'OffshorePipelay:SEV!$c$3:$c999) to your search term ($e$10) and
evaluate it as a 1 when it matches and a 0 when it does not. The
sumproduct formula allows multiple conditions, and then does the usual
sumproduct thing: it multiplies the 1s and 0s and adds them together
at the end. If a row has a match in one array and a non-match on the
same row in the other array, the formula multiplies 1 by 0, resulting
in 0 and adding nothing to the final count. If a row contains a match
in both arrays, the formula multiplies 1 by 1, resulting in 1 and
adding 1 to the final count.

The double unary operator is two dashes inside your formula. Your
formula should work if you enter this:
SUMPRODUCT(--('OffshorePipelay:SEV'!$C$3:$C$999=$E$10),--
('OffshorePipelay:SEV!$R$3:$R$999=$C15))

Note that I replaced the * multiplication operator with a comma, and
added two double unary operators before the open parens.

Good luck, let us know how it works.

DaveO
Eschew obfuscation.