Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
Within the attached array formula, I need to either perform a lookup, or use
the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
Hi, Biff.
Thanks for your assistance - Regarding the "redundancy" - the second IF was the ELSE of the first - If the column B is not null, then add all occurrences of column B instead of applying a lookup. I have taken your advice, and "played" almost all day - herewith what I have now - but this is still not giving me what I expect - the numbers make no sense at all, as I am expecting 48 as a total, and only get 8 (7 and 1 for each of the SumProduct functions below). Logic Explanation When column B (Queue) of the Defects file is null, I need to use its column C (Queue Owner) to lookup a value in the QueueOwnerList file, and then only those Queues from that file that are equal to the value in my current sheet's B12. If column B of the Defects file is not null, simply add to the count those queues that are equal to B12 (PS. this second SumProduct works 100%). =SUMPRODUCT(--('[Defects 20070604.xls]Sheet1'!$A$1:$A$2000="OMS"), --('[Defects 20070604.xls]Sheet1'!$B$1:$B$2000=""), --(ISNUMBER(MATCH('[Defects 20070604.xls]Sheet1'!$C$1:$C$2000,'[QueueOwnerList.xls]lookup names'!$A$1:$A$2000,0))), --('[QueueOwnerList.xls]lookup names'!$B$1:$B$2000=$B12))+ SUMPRODUCT(--('[Defects 20070604.xls]Sheet1'!$A$1:$A$2000="OMS"), --('[Defects 20070604.xls]Sheet1'!$B$1:$B$2000=$B12)) Am I misunderstanding something or doing something totally stupid? Kind regards, Pete. "T. Valko" wrote: You have conflicting conditions: IF($B$1:$B$2000="" IF($B9=$B$1:$B$2000 Unless B9 is empty, in which case those expressions would be redundant, those expressions can't be TRUE at the same time. The VLOOKUP/INDEX can be expressed in a SUMPRODUCT function as: --(ISNUMBER(MATCH(C1:C2000,'[Lookupfile.xls]Name'!$A$1:$A$2000,0))), --('[Lookupfile.xls]Name'!$B$1:$B$2000=$B9) But, you have to straighten out those IF(B1....... things! Biff "Crazy Pete" <Crazy wrote in message ... Within the attached array formula, I need to either perform a lookup, or use the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
Define: null
Empty cell? Numeric 0? Blank cell (as in a formula blank "")? For ease of reading let's leave out all the file names/sheet names. Try logic like this: =SUMPRODUCT(--(A1:A10="oms"),--(B1:B10=B12))+ =SUMPRODUCT(--(A1:A10="oms"),--(B1:B10=""),--(ISNUMBER(MATCH(C1:C10,H1:H10,0))),--(I1:I10=B12)) Whe C1:C10 = the range you used in your VLOOKUP H1:I10 = the lookup_table Biff "Crazy Pete" wrote in message ... Hi, Biff. Thanks for your assistance - Regarding the "redundancy" - the second IF was the ELSE of the first - If the column B is not null, then add all occurrences of column B instead of applying a lookup. I have taken your advice, and "played" almost all day - herewith what I have now - but this is still not giving me what I expect - the numbers make no sense at all, as I am expecting 48 as a total, and only get 8 (7 and 1 for each of the SumProduct functions below). Logic Explanation When column B (Queue) of the Defects file is null, I need to use its column C (Queue Owner) to lookup a value in the QueueOwnerList file, and then only those Queues from that file that are equal to the value in my current sheet's B12. If column B of the Defects file is not null, simply add to the count those queues that are equal to B12 (PS. this second SumProduct works 100%). =SUMPRODUCT(--('[Defects 20070604.xls]Sheet1'!$A$1:$A$2000="OMS"), --('[Defects 20070604.xls]Sheet1'!$B$1:$B$2000=""), --(ISNUMBER(MATCH('[Defects 20070604.xls]Sheet1'!$C$1:$C$2000,'[QueueOwnerList.xls]lookup names'!$A$1:$A$2000,0))), --('[QueueOwnerList.xls]lookup names'!$B$1:$B$2000=$B12))+ SUMPRODUCT(--('[Defects 20070604.xls]Sheet1'!$A$1:$A$2000="OMS"), --('[Defects 20070604.xls]Sheet1'!$B$1:$B$2000=$B12)) Am I misunderstanding something or doing something totally stupid? Kind regards, Pete. "T. Valko" wrote: You have conflicting conditions: IF($B$1:$B$2000="" IF($B9=$B$1:$B$2000 Unless B9 is empty, in which case those expressions would be redundant, those expressions can't be TRUE at the same time. The VLOOKUP/INDEX can be expressed in a SUMPRODUCT function as: --(ISNUMBER(MATCH(C1:C2000,'[Lookupfile.xls]Name'!$A$1:$A$2000,0))), --('[Lookupfile.xls]Name'!$B$1:$B$2000=$B9) But, you have to straighten out those IF(B1....... things! Biff "Crazy Pete" <Crazy wrote in message ... Within the attached array formula, I need to either perform a lookup, or use the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
I'm having a similar problem. I have 2 tables. One tables is of products
and options. The data of this table are the part #s for the product with that option. The second table is of part #s and their prices. I'm trying to SUM all VLOOKUPs of all products for a particular option. In the example below, I can't seem to get the TOTAL line working in the first table. My current formula for the total of Option1 column is: =SUM(VLOOKUP(B2:B5,$A$9:$B$21,2,FALSE)) ==VIEW THIS IN FIXED TEXT== For example: Product Option1 Option2 A 1 2 B 3 4 C 5 6 TOTAL $45 $60 Part Price 1 $5 2 $10 3 $15 4 $20 5 $25 6 $30 "Crazy Pete" wrote: Within the attached array formula, I need to either perform a lookup, or use the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
See this screencap:
http://img156.imageshack.us/img156/8795/sumpoy3.jpg Copy the formula in B5 across as needed. Biff "Hossy" wrote in message ... I'm having a similar problem. I have 2 tables. One tables is of products and options. The data of this table are the part #s for the product with that option. The second table is of part #s and their prices. I'm trying to SUM all VLOOKUPs of all products for a particular option. In the example below, I can't seem to get the TOTAL line working in the first table. My current formula for the total of Option1 column is: =SUM(VLOOKUP(B2:B5,$A$9:$B$21,2,FALSE)) ==VIEW THIS IN FIXED TEXT== For example: Product Option1 Option2 A 1 2 B 3 4 C 5 6 TOTAL $45 $60 Part Price 1 $5 2 $10 3 $15 4 $20 5 $25 6 $30 "Crazy Pete" wrote: Within the attached array formula, I need to either perform a lookup, or use the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
Thank you very much! You're awesome!
One thing to note however, it won't handle duplicate parts in the same column. "T. Valko" wrote: See this screencap: http://img156.imageshack.us/img156/8795/sumpoy3.jpg Copy the formula in B5 across as needed. Biff "Hossy" wrote in message ... I'm having a similar problem. I have 2 tables. One tables is of products and options. The data of this table are the part #s for the product with that option. The second table is of part #s and their prices. I'm trying to SUM all VLOOKUPs of all products for a particular option. In the example below, I can't seem to get the TOTAL line working in the first table. My current formula for the total of Option1 column is: =SUM(VLOOKUP(B2:B5,$A$9:$B$21,2,FALSE)) ==VIEW THIS IN FIXED TEXT== For example: Product Option1 Option2 A 1 2 B 3 4 C 5 6 TOTAL $45 $60 Part Price 1 $5 2 $10 3 $15 4 $20 5 $25 6 $30 "Crazy Pete" wrote: Within the attached array formula, I need to either perform a lookup, or use the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
it won't handle duplicate parts in the same column.
Do you mean like this: http://img162.imageshack.us/img162/5617/sump1wt8.jpg Notice how the formula uses a range that is 1 cell longer than the range for the price. As long as this "extra" cell does not contain an error value (#NUM!, #N/A, #VALUE!, etc) there is no problem. Biff "Hossy" wrote in message ... Thank you very much! You're awesome! One thing to note however, it won't handle duplicate parts in the same column. "T. Valko" wrote: See this screencap: http://img156.imageshack.us/img156/8795/sumpoy3.jpg Copy the formula in B5 across as needed. Biff "Hossy" wrote in message ... I'm having a similar problem. I have 2 tables. One tables is of products and options. The data of this table are the part #s for the product with that option. The second table is of part #s and their prices. I'm trying to SUM all VLOOKUPs of all products for a particular option. In the example below, I can't seem to get the TOTAL line working in the first table. My current formula for the total of Option1 column is: =SUM(VLOOKUP(B2:B5,$A$9:$B$21,2,FALSE)) ==VIEW THIS IN FIXED TEXT== For example: Product Option1 Option2 A 1 2 B 3 4 C 5 6 TOTAL $45 $60 Part Price 1 $5 2 $10 3 $15 4 $20 5 $25 6 $30 "Crazy Pete" wrote: Within the attached array formula, I need to either perform a lookup, or use the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
This one's better and will even handle text: (don't know why I didn't think
of this before!): =SUMPRODUCT(SUMIF($G2:$G7,B2:B4,$H2:$H7)) Biff "T. Valko" wrote in message ... it won't handle duplicate parts in the same column. Do you mean like this: http://img162.imageshack.us/img162/5617/sump1wt8.jpg Notice how the formula uses a range that is 1 cell longer than the range for the price. As long as this "extra" cell does not contain an error value (#NUM!, #N/A, #VALUE!, etc) there is no problem. Biff "Hossy" wrote in message ... Thank you very much! You're awesome! One thing to note however, it won't handle duplicate parts in the same column. "T. Valko" wrote: See this screencap: http://img156.imageshack.us/img156/8795/sumpoy3.jpg Copy the formula in B5 across as needed. Biff "Hossy" wrote in message ... I'm having a similar problem. I have 2 tables. One tables is of products and options. The data of this table are the part #s for the product with that option. The second table is of part #s and their prices. I'm trying to SUM all VLOOKUPs of all products for a particular option. In the example below, I can't seem to get the TOTAL line working in the first table. My current formula for the total of Option1 column is: =SUM(VLOOKUP(B2:B5,$A$9:$B$21,2,FALSE)) ==VIEW THIS IN FIXED TEXT== For example: Product Option1 Option2 A 1 2 B 3 4 C 5 6 TOTAL $45 $60 Part Price 1 $5 2 $10 3 $15 4 $20 5 $25 6 $30 "Crazy Pete" wrote: Within the attached array formula, I need to either perform a lookup, or use the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
The SUMIF works perfectly, even with duplicates. Much simpler too. :-)
Thank you very much. What are the "--" called in the first SUMPRODUCT function you sent me? I've never seen that notation in Excel before. "T. Valko" wrote: This one's better and will even handle text: (don't know why I didn't think of this before!): =SUMPRODUCT(SUMIF($G2:$G7,B2:B4,$H2:$H7)) Biff "T. Valko" wrote in message ... it won't handle duplicate parts in the same column. Do you mean like this: http://img162.imageshack.us/img162/5617/sump1wt8.jpg Notice how the formula uses a range that is 1 cell longer than the range for the price. As long as this "extra" cell does not contain an error value (#NUM!, #N/A, #VALUE!, etc) there is no problem. Biff "Hossy" wrote in message ... Thank you very much! You're awesome! One thing to note however, it won't handle duplicate parts in the same column. "T. Valko" wrote: See this screencap: http://img156.imageshack.us/img156/8795/sumpoy3.jpg Copy the formula in B5 across as needed. Biff "Hossy" wrote in message ... I'm having a similar problem. I have 2 tables. One tables is of products and options. The data of this table are the part #s for the product with that option. The second table is of part #s and their prices. I'm trying to SUM all VLOOKUPs of all products for a particular option. In the example below, I can't seem to get the TOTAL line working in the first table. My current formula for the total of Option1 column is: =SUM(VLOOKUP(B2:B5,$A$9:$B$21,2,FALSE)) ==VIEW THIS IN FIXED TEXT== For example: Product Option1 Option2 A 1 2 B 3 4 C 5 6 TOTAL $45 $60 Part Price 1 $5 2 $10 3 $15 4 $20 5 $25 6 $30 "Crazy Pete" wrote: Within the attached array formula, I need to either perform a lookup, or use the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
Double unary minus. Google for details.
-- David Biddulph "Hossy" wrote in message ... .... What are the "--" called in the first SUMPRODUCT function you sent me? I've never seen that notation in Excel before. .... |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference certain rows within array formulae?
See these about the "--":
http://xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html Biff "Hossy" wrote in message ... The SUMIF works perfectly, even with duplicates. Much simpler too. :-) Thank you very much. What are the "--" called in the first SUMPRODUCT function you sent me? I've never seen that notation in Excel before. "T. Valko" wrote: This one's better and will even handle text: (don't know why I didn't think of this before!): =SUMPRODUCT(SUMIF($G2:$G7,B2:B4,$H2:$H7)) Biff "T. Valko" wrote in message ... it won't handle duplicate parts in the same column. Do you mean like this: http://img162.imageshack.us/img162/5617/sump1wt8.jpg Notice how the formula uses a range that is 1 cell longer than the range for the price. As long as this "extra" cell does not contain an error value (#NUM!, #N/A, #VALUE!, etc) there is no problem. Biff "Hossy" wrote in message ... Thank you very much! You're awesome! One thing to note however, it won't handle duplicate parts in the same column. "T. Valko" wrote: See this screencap: http://img156.imageshack.us/img156/8795/sumpoy3.jpg Copy the formula in B5 across as needed. Biff "Hossy" wrote in message ... I'm having a similar problem. I have 2 tables. One tables is of products and options. The data of this table are the part #s for the product with that option. The second table is of part #s and their prices. I'm trying to SUM all VLOOKUPs of all products for a particular option. In the example below, I can't seem to get the TOTAL line working in the first table. My current formula for the total of Option1 column is: =SUM(VLOOKUP(B2:B5,$A$9:$B$21,2,FALSE)) ==VIEW THIS IN FIXED TEXT== For example: Product Option1 Option2 A 1 2 B 3 4 C 5 6 TOTAL $45 $60 Part Price 1 $5 2 $10 3 $15 4 $20 5 $25 6 $30 "Crazy Pete" wrote: Within the attached array formula, I need to either perform a lookup, or use the data I have per iteration, to count for various values in column B. However, as I understand, VLOOKUP requires a single value or reference as the lookup_value. I have tried using the INDEX function (see below), but it appears to only pick up row 1 for each iteration, hence not matching with any valid lookup values. How do I, inside an array calculation, specify the row which has been found to match the preceding IF statements? Effectively, I wish to perform a lookup on another workbook, using column C and the row which has been found to match the two preceding IF statements. =SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0)) regards, Pete. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying formulae along rows. | Excel Discussion (Misc queries) | |||
How to concatenate an array of four stings using formulae (not UDF) | Excel Worksheet Functions | |||
Can you have fixed cell reference when dragging/copying formulae? | Excel Discussion (Misc queries) | |||
OFFSET and array formulae | Excel Discussion (Misc queries) | |||
Formulae to select periodic rows | Excel Worksheet Functions |