Prev: HLOOKUP into another xls file
Next: Question about syntax for conditional formatting (RESOLVED)
From: Bob Phillips on 22 Mar 2010 20:02
Paul,

A point to note if you use multiple columns in the range being tested in
SUMPRODUCT, don't use the double unary form
(--(rng1=condition2),--(-rng2=condition2)), use the multiplication operator.

--

HTH

Bob

"Paul C" <PaulC(a)discussions.microsoft.com> wrote in message
news:395C5427-6A38-4BB8-BF4E-C0C78FFC9E9A(a)microsoft.com...
> Just a quick note
>
> Bob's method is the better of the two, Offset is a volitile function and
> can
> bog down large sheets with calculations.
>
> I did not even think to use SUMPRODUCT with a condition across two
> columns.
> I use conditional SUMPRODUCT all the time, but my conditions are limited
> to
> individual columns. A very useful trick to remember. I can go home now,
> I
> learned something today.
>
> --
> If this helps, please remember to click yes.
>
>
> "eflip" wrote:
>
>> Thank you both Paul and Bob - both formulas worked perfectly!
>> Thanks for the quick response as well.
>>
>> "Bob Phillips" wrote:
>>
>> > Try this
>> >
>> > =IF(SUMPRODUCT((Sheet1!$A$2:$A$20=A2)*(Sheet1!$B$2:$C$20=B2)),"Qualifying",
>> > IF(SUMPRODUCT((Sheet1!$A$2:$A$20=A2)*(Sheet1!$D$2:$E$20=B2)),"Non-qualifying",""))
>> >
>> > --
>> >
>> > HTH
>> >
>> > Bob
>> >
>> > "eflip" <eflip(a)discussions.microsoft.com> wrote in message
>> > news:5B78F8FD-77DD-44D6-A8B1-10F87B37CE35(a)microsoft.com...
>> > > Hello -
>> > >
>> > > I have 2 tabs of data.
>> > > The first tab has info by product and its qualifying vendors and
>> > > non-qualifying vendors (the vendors are in seperate columns)
>> > > ColumnA ColumnB ColumnsC ColumnD ColumnE
>> > > Product X Vendor1 Vendor2 Vendor3 Vendor4
>> > >
>> > > Column A will always be unique
>> > > Columns B and C are qualifying vendors and Column D and E are
>> > > non-qualifying.
>> > >
>> > > In the second tab I have the following:
>> > > ColumnA ColumnB
>> > > Product Vendor
>> > >
>> > > What formula do I use to tell me if the vendor is Qualifying (Q) or
>> > > Non-Qualifying (N) or blank if the vendor is not included in the
>> > > list.
>> > >
>> > > Any help would be greatly appreciated!
>> > >
>> > > Thank you in advance.
>> > >
>> > >
>> >
>> >
>> > .
>> >


First  |  Prev  | 
Pages: 1 2
Prev: HLOOKUP into another xls file
Next: Question about syntax for conditional formatting (RESOLVED)