Can anyone possibly help with the following?

I have the formula below which counts the total number of items which
contain two specific criteria in the worksheet named OffshorePipelay.

SUMPRODUCT(('OffshorePipelay'!$C$3:$C$999=$E$10)*( 'Offshore Pipelay'!$R
$3:$R$999=$C15))

Where the value in E10 in the formula is selected from a dropdown list
of 10 possible entries.

I have 10 worksheets each one named for each of these possible entries
and would like the formula to search each of these multiple worksheets
and find which one of these matches this specific criteria and then
count how many there are when both this and the second criteria (in
C15) are met.

I have tried typing this in:

SUMPRODUCT(('OffshorePipelay:SEV'!$C$3:$C$999=$E$1 0)*('Offshore
Pipelay:SEV!$R$3:$R$999=$C15))

But when I press enter the formula comes up as this:

SUMPRODUCT(('OffshorePipelay:[SEV]SEV'!$C$3:$C$999=$E$10)*('Offshore
Pipelay:[SEV]SEV'!$R$3:$R$999=$C15))

And the value comes up as #REF!

Can anyone shed some light as to where I’m going wrong as I have quite
a few formulas where searching across all the worksheets would save
ALOT of time.

Thanks in advance for any input!

LCTECH