Require formula to search across multiple worksheets

[LCTECH001]

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$10)*('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

 

[Dave O]

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.

 

[Dave O]

On looking at it further: Excel should not allow you to include a
colon ":" in your worksheet name. Try removing the colon, and making
sure the typed formula (everything between the apostrophe and before
the exclamation point) exactly matches the worksheet name.

Dave O