software

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=quot;Clquot;)),--(Overall!D1100=C2),Overall!H1:H100)

Biff

quot;roy.okinawaquot; gt; wrote in message
...
gt; Worksheet to input formula: Exception Sheet
gt; Worksheet containing data to find/sum: Overall
gt;
gt; I want to find on the Overall worksheet in column B any text beginning
gt; with
gt; Cl and in column D any number matching the number I type on the Exception
gt; sheet in C2 . Then I want to match those finding against the number of
gt; days
gt; in shop, column H, of the Overall worksheet, and sum the days in shop.
gt;
gt;
Typo in one of the range references:

LEFT(Overall!B1:B110=quot;Clquot;))

Should be:

LEFT(Overall!B1:B100=quot;Clquot;))

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT(--(LEFT(Overall!B1:B110=quot;Clquot;)),--(Overall!D1100=C2),Overall!H1:H100)
gt;
gt; Biff
gt;
gt; quot;roy.okinawaquot; gt; wrote in message
gt; ...
gt;gt; Worksheet to input formula: Exception Sheet
gt;gt; Worksheet containing data to find/sum: Overall
gt;gt;
gt;gt; I want to find on the Overall worksheet in column B any text beginning
gt;gt; with
gt;gt; Cl and in column D any number matching the number I type on the Exception
gt;gt; sheet in C2 . Then I want to match those finding against the number of
gt;gt; days
gt;gt; in shop, column H, of the Overall worksheet, and sum the days in shop.
gt;gt;
gt;gt;
gt;
gt;
I get the error of #VALUE!.

Here is the formula I input based on what you provided:
=SUMPRODUCT(--(LEFT(Overall!B8:B1000=quot;Clquot;)),--(Overall!D81000=C2),Overall!H8:H1000)
quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT(--(LEFT(Overall!B1:B110=quot;Clquot;)),--(Overall!D1100=C2),Overall!H1:H100)
gt;
gt; Biff
gt;
gt; quot;roy.okinawaquot; gt; wrote in message
gt; ...
gt; gt; Worksheet to input formula: Exception Sheet
gt; gt; Worksheet containing data to find/sum: Overall
gt; gt;
gt; gt; I want to find on the Overall worksheet in column B any text beginning
gt; gt; with
gt; gt; Cl and in column D any number matching the number I type on the Exception
gt; gt; sheet in C2 . Then I want to match those finding against the number of
gt; gt; days
gt; gt; in shop, column H, of the Overall worksheet, and sum the days in shop.
gt; gt;
gt; gt;
gt;
gt;
gt;

Ooops!

My fault, I gave you a bad formula!

Try this:

=SUMPRODUCT(--(LEFT(Overall!B8:B1000,2)=quot;Clquot;),--(Overall!D81000=C2),Overall!H8:H1000)

Biff

quot;roy.okinawaquot; gt; wrote in message
...
gt;I get the error of #VALUE!.
gt;
gt; Here is the formula I input based on what you provided:
gt; =SUMPRODUCT(--(LEFT(Overall!B8:B1000=quot;Clquot;)),--(Overall!D81000=C2),Overall!H8:H1000)
gt;
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =SUMPRODUCT(--(LEFT(Overall!B1:B110=quot;Clquot;)),--(Overall!D1100=C2),Overall!H1:H100)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;roy.okinawaquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Worksheet to input formula: Exception Sheet
gt;gt; gt; Worksheet containing data to find/sum: Overall
gt;gt; gt;
gt;gt; gt; I want to find on the Overall worksheet in column B any text beginning
gt;gt; gt; with
gt;gt; gt; Cl and in column D any number matching the number I type on the
gt;gt; gt; Exception
gt;gt; gt; sheet in C2 . Then I want to match those finding against the number of
gt;gt; gt; days
gt;gt; gt; in shop, column H, of the Overall worksheet, and sum the days in shop.
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
Thank did it.

Thanks.

quot;Biffquot; wrote:

gt; Ooops!
gt;
gt; My fault, I gave you a bad formula!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT(--(LEFT(Overall!B8:B1000,2)=quot;Clquot;),--(Overall!D81000=C2),Overall!H8:H1000)
gt;
gt; Biff
gt;
gt; quot;roy.okinawaquot; gt; wrote in message
gt; ...
gt; gt;I get the error of #VALUE!.
gt; gt;
gt; gt; Here is the formula I input based on what you provided:
gt; gt; =SUMPRODUCT(--(LEFT(Overall!B8:B1000=quot;Clquot;)),--(Overall!D81000=C2),Overall!H8:H1000)
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; Try this:
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(LEFT(Overall!B1:B110=quot;Clquot;)),--(Overall!D1100=C2),Overall!H1:H100)
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;roy.okinawaquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Worksheet to input formula: Exception Sheet
gt; gt;gt; gt; Worksheet containing data to find/sum: Overall
gt; gt;gt; gt;
gt; gt;gt; gt; I want to find on the Overall worksheet in column B any text beginning
gt; gt;gt; gt; with
gt; gt;gt; gt; Cl and in column D any number matching the number I type on the
gt; gt;gt; gt; Exception
gt; gt;gt; gt; sheet in C2 . Then I want to match those finding against the number of
gt; gt;gt; gt; days
gt; gt;gt; gt; in shop, column H, of the Overall worksheet, and sum the days in shop.
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

You're welcome

Biff

quot;roy.okinawaquot; gt; wrote in message
...
gt; Thank did it.
gt;
gt; Thanks.
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Ooops!
gt;gt;
gt;gt; My fault, I gave you a bad formula!
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =SUMPRODUCT(--(LEFT(Overall!B8:B1000,2)=quot;Clquot;),--(Overall!D81000=C2),Overall!H8:H1000)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;roy.okinawaquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I get the error of #VALUE!.
gt;gt; gt;
gt;gt; gt; Here is the formula I input based on what you provided:
gt;gt; gt; =SUMPRODUCT(--(LEFT(Overall!B8:B1000=quot;Clquot;)),--(Overall!D81000=C2),Overall!H8:H1000)
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi!
gt;gt; gt;gt;
gt;gt; gt;gt; Try this:
gt;gt; gt;gt;
gt;gt; gt;gt; =SUMPRODUCT(--(LEFT(Overall!B1:B110=quot;Clquot;)),--(Overall!D1100=C2),Overall!H1:H100)
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt; quot;roy.okinawaquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; Worksheet to input formula: Exception Sheet
gt;gt; gt;gt; gt; Worksheet containing data to find/sum: Overall
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I want to find on the Overall worksheet in column B any text
gt;gt; gt;gt; gt; beginning
gt;gt; gt;gt; gt; with
gt;gt; gt;gt; gt; Cl and in column D any number matching the number I type on the
gt;gt; gt;gt; gt; Exception
gt;gt; gt;gt; gt; sheet in C2 . Then I want to match those finding against the number
gt;gt; gt;gt; gt; of
gt;gt; gt;gt; gt; days
gt;gt; gt;gt; gt; in shop, column H, of the Overall worksheet, and sum the days in
gt;gt; gt;gt; gt; shop.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;