G

#### Guest

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),--(D15="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

A1014

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 A1014 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"),D1014)

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