Sumproduct from different worksheet
 

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.

Sumproduct from different worksheet
 

Hi!

Try this:

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

Biff

"roy.okinawa" wrote in message
...
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.

Sumproduct from different worksheet
 

Typo in one of the range references:

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

Should be:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"roy.okinawa" wrote in message
...
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.

Sumproduct from different worksheet
 

I get the error of #VALUE!.

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



"Biff" wrote:

Hi!

Try this:

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

Biff

"roy.okinawa" wrote in message
...
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.

Sumproduct from different worksheet
 

Ooops!

My fault, I gave you a bad formula!

Try this:

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

Biff

"roy.okinawa" wrote in message
...
I get the error of #VALUE!.

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



"Biff" wrote:

Hi!

Try this:

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

Biff

"roy.okinawa" wrote in message
...
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.

Sumproduct from different worksheet
 

Thank did it.

Thanks.

"Biff" wrote:

Ooops!

My fault, I gave you a bad formula!

Try this:

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

Biff

"roy.okinawa" wrote in message
...
I get the error of #VALUE!.

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



"Biff" wrote:

Hi!

Try this:

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

Biff

"roy.okinawa" wrote in message
...
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.

Sumproduct from different worksheet
 

You're welcome

Biff

"roy.okinawa" wrote in message
...
Thank did it.

Thanks.

"Biff" wrote:

Ooops!

My fault, I gave you a bad formula!

Try this:

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

Biff

"roy.okinawa" wrote in message
...
I get the error of #VALUE!.

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



"Biff" wrote:

Hi!

Try this:

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

Biff

"roy.okinawa" wrote in message
...
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.