Sumproduct from different worksheet

[Guest]

Worksheet to input formula: Exception Sheet
Worksheet containing data to find/sum: Overall

I want to find on the Overall worksheet in column B any text beginning with
Cl and in column D any number matching the number I type on the Exception
sheet in C2 . Then I want to match those finding against the number of days
in shop, column H, of the Overall worksheet, and sum the days in shop.

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(LEFT(Overall!B1:B110="Cl")),--(Overall!D1100=C2),Overall!H1:H100)

Biff

 

[Biff]

Typo in one of the range references:

LEFT(Overall!B1:B110="Cl"))

Should be:

LEFT(Overall!B1:B100="Cl"))

Biff

 

[Guest]

I get the error of #VALUE!.

Here is the formula I input based on what you provided:
=SUMPRODUCT(--(LEFT(Overall!B8:B1000="Cl")),--(Overall!D81000=C2),Overall!H8:H1000)

 

[Biff]

Ooops!

My fault, I gave you a bad formula!

Try this:

=SUMPRODUCT(--(LEFT(Overall!B8:B1000,2)="Cl"),--(Overall!D81000=C2),Overall!H8:H1000)

Biff

 

[Guest]

Thank did it.

Thanks.

Ooops!

My fault, I gave you a bad formula!

Try this:

=SUMPRODUCT(--(LEFT(Overall!B8:B1000,2)="Cl"),--(Overall!D81000=C2),Overall!H8:H1000)

Biff

 

[Biff]

You're welcome

Biff

Thank did it.

Thanks.