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" <royokinawa@discussions.microsoft.com> wrote in message
> news:999F85ED-1504-4DCC-8E2C-40DE75DFA565@microsoft.com...
> > 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" <royokinawa@discussions.microsoft.com> wrote in message
news:8D1E8E8C-F2F0-4006-A37C-BD4C8142E3EF@microsoft.com...
>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" <royokinawa@discussions.microsoft.com> wrote in message
>> news:999F85ED-1504-4DCC-8E2C-40DE75DFA565@microsoft.com...
>> > 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" <royokinawa@discussions.microsoft.com> wrote in message
> news:8D1E8E8C-F2F0-4006-A37C-BD4C8142E3EF@microsoft.com...
> >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" <royokinawa@discussions.microsoft.com> wrote in message
> >> news:999F85ED-1504-4DCC-8E2C-40DE75DFA565@microsoft.com...
> >> > 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" <royokinawa@discussions.microsoft.com> wrote in message
news:9B48330E-2A1F-4F0D-9B90-CD06EE6395AA@microsoft.com...
> 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" <royokinawa@discussions.microsoft.com> wrote in message
>> news:8D1E8E8C-F2F0-4006-A37C-BD4C8142E3EF@microsoft.com...
>> >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" <royokinawa@discussions.microsoft.com> wrote in message
>> >> news:999F85ED-1504-4DCC-8E2C-40DE75DFA565@microsoft.com...
>> >> > 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.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>