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.

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.

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.

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.

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.

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.

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.