Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple condition lookup and match cell format | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Can I set up a formula to lookup a value for a condition | Excel Discussion (Misc queries) | |||
Multiple Condition Formula | Excel Worksheet Functions | |||
Multiple Condition Sumif Formula | Excel Worksheet Functions |