Multiple Lookup as condition in sumproduct formula
 

Sorry for the repost but I cannot seem to figure this out. I keep running
into this and there has to be a solution. Lets say I have the following data
in A1:E5:

Option 1 TRUE BLUE On 10
Option 2 TRUE BLACK Off 15
Option 3 FALSE Red On 5
Option 1 TRUE Red Off 7
Option 2 TRUE Red Off 4

I can query the aray as follows in A7 using a sumproduc with mulitple
conditions:

=SUMPRODUCT(--(C1:C5="RED"),--(B1:B5=TRUE),--(D1:D5="OFF"),E1:E5) =11

However lets assuming I do not have the TRUE/FALSE field in column B. But
lets assume that I do have the Option field in Column A still. New Range is
A10:D14

Option 1 BLUE On 10
Option 2 BLACK Off 15
Option 3 Red On 5
Option 1 Red Off 7
Option 2 Red Off 4

But elsewhere in my spreadsheet say G10:H12 I have lookup table:
Option 1 TRUE
Option 2 TRUE
Option 3 FALSE

Now I want to query the range in A10:D14 but I want to incorporate the
Option field into the sumproduct and the associated True/False values.
However I need to do a lookup in range G10:G12 to see which boolean is
associated with each Option. Including the LOOKUP below works:

=SUMPRODUCT(--(LOOKUP(A10:A14,G10:H12)),--(B10:B14="RED"),--(C10:C14="OFF"),D10:D14)

However this only works if the values in G10:H12 are sorted based on the
items in G10:G12. But I cannot guarantee that these will be sorted.

Is there another sort of lookup I can use within the sumproduct which does
not rely on a sorted range in G10:G12? Or is there a way to sort the data in
G10:G12 using an excel function (Say an array formula in I10:J12) prior to
pulling it into the sumproduct formula?

Thanks

EM

Multiple Lookup as condition in sumproduct formula
 

Use MATCH instead of LOOKUP?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"ExcelMonkey" wrote:

Sorry for the repost but I cannot seem to figure this out. I keep running
into this and there has to be a solution. Lets say I have the following data
in A1:E5:

Option 1 TRUE BLUE On 10
Option 2 TRUE BLACK Off 15
Option 3 FALSE Red On 5
Option 1 TRUE Red Off 7
Option 2 TRUE Red Off 4

I can query the aray as follows in A7 using a sumproduc with mulitple
conditions:

=SUMPRODUCT(--(C1:C5="RED"),--(B1:B5=TRUE),--(D1:D5="OFF"),E1:E5) =11

However lets assuming I do not have the TRUE/FALSE field in column B. But
lets assume that I do have the Option field in Column A still. New Range is
A10:D14

Option 1 BLUE On 10
Option 2 BLACK Off 15
Option 3 Red On 5
Option 1 Red Off 7
Option 2 Red Off 4

But elsewhere in my spreadsheet say G10:H12 I have lookup table:
Option 1 TRUE
Option 2 TRUE
Option 3 FALSE

Now I want to query the range in A10:D14 but I want to incorporate the
Option field into the sumproduct and the associated True/False values.
However I need to do a lookup in range G10:G12 to see which boolean is
associated with each Option. Including the LOOKUP below works:

=SUMPRODUCT(--(LOOKUP(A10:A14,G10:H12)),--(B10:B14="RED"),--(C10:C14="OFF"),D10:D14)

However this only works if the values in G10:H12 are sorted based on the
items in G10:G12. But I cannot guarantee that these will be sorted.

Is there another sort of lookup I can use within the sumproduct which does
not rely on a sorted range in G10:G12? Or is there a way to sort the data in
G10:G12 using an excel function (Say an array formula in I10:J12) prior to
pulling it into the sumproduct formula?

Thanks

EM

Multiple Lookup as condition in sumproduct formula
 

Match will return he position of a variable. What are you suggesting? If I
do the following in an array formula:

={MATCH(A10:A14,G10:G12,0)}

It does not return an array. Even if it did, it would provided an array of
positions. I Would then have wrap a function around this to turn these
positions into associated cell values in the corresponding columns.

Thanks

EM

"Dave F" wrote:

Use MATCH instead of LOOKUP?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"ExcelMonkey" wrote:

Sorry for the repost but I cannot seem to figure this out. I keep running
into this and there has to be a solution. Lets say I have the following data
in A1:E5:

Option 1 TRUE BLUE On 10
Option 2 TRUE BLACK Off 15
Option 3 FALSE Red On 5
Option 1 TRUE Red Off 7
Option 2 TRUE Red Off 4

I can query the aray as follows in A7 using a sumproduc with mulitple
conditions:

=SUMPRODUCT(--(C1:C5="RED"),--(B1:B5=TRUE),--(D1:D5="OFF"),E1:E5) =11

However lets assuming I do not have the TRUE/FALSE field in column B. But
lets assume that I do have the Option field in Column A still. New Range is
A10:D14

Option 1 BLUE On 10
Option 2 BLACK Off 15
Option 3 Red On 5
Option 1 Red Off 7
Option 2 Red Off 4

But elsewhere in my spreadsheet say G10:H12 I have lookup table:
Option 1 TRUE
Option 2 TRUE
Option 3 FALSE

Now I want to query the range in A10:D14 but I want to incorporate the
Option field into the sumproduct and the associated True/False values.
However I need to do a lookup in range G10:G12 to see which boolean is
associated with each Option. Including the LOOKUP below works:

=SUMPRODUCT(--(LOOKUP(A10:A14,G10:H12)),--(B10:B14="RED"),--(C10:C14="OFF"),D10:D14)

However this only works if the values in G10:H12 are sorted based on the
items in G10:G12. But I cannot guarantee that these will be sorted.

Is there another sort of lookup I can use within the sumproduct which does
not rely on a sorted range in G10:G12? Or is there a way to sort the data in
G10:G12 using an excel function (Say an array formula in I10:J12) prior to
pulling it into the sumproduct formula?

Thanks

EM