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.

Hi!

Try this:

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

Biff

"roy.okinawa" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(LEFT(Overall!B1:B110="Cl")),--(Overall!D1:D100=C2),Overall!H1:H100)
>
> Biff
>
> "roy.okinawa" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>