Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT has never let me down BUT...
Here's the deal: I know SUMPRODUCT is capable of comparing multiple values
against an array by using {}'s like this: Cell A1: SUMPRODUCT((ARRAY={Option1,Option2,Option3})*ARRAY 2SUM) BUT can the criteria range be made to reference a cell value??? Something like this: Cell A1: SUMPRODUCT((ARRAY={A2})*ARRAY2SUM) Cell A2: "Option1,Option2,Option3" This would be soooo helpful if possible. Thanks in advance. -- John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT has never let me down BUT...
You cannot use {} syntax with a cell reference. However, if Array and
Array2SUM are appropriately sized, then SUMPRODUCT((ARRAY=A1)*ARRAY2SUM) will work. Jerry "JM" wrote: Here's the deal: I know SUMPRODUCT is capable of comparing multiple values against an array by using {}'s like this: Cell A1: SUMPRODUCT((ARRAY={Option1,Option2,Option3})*ARRAY 2SUM) BUT can the criteria range be made to reference a cell value??? Something like this: Cell A1: SUMPRODUCT((ARRAY={A2})*ARRAY2SUM) Cell A2: "Option1,Option2,Option3" This would be soooo helpful if possible. Thanks in advance. -- John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT has never let me down BUT...
Array constants can't be references.
List your criteria in cells: F1 = option1 F2 = option2 F3 = option3 =SUMPRODUCT(--(ISNUMBER(MATCH(Array1,F1:F3,0))),Array2) Biff "JM" wrote in message m... Here's the deal: I know SUMPRODUCT is capable of comparing multiple values against an array by using {}'s like this: Cell A1: SUMPRODUCT((ARRAY={Option1,Option2,Option3})*ARRAY 2SUM) BUT can the criteria range be made to reference a cell value??? Something like this: Cell A1: SUMPRODUCT((ARRAY={A2})*ARRAY2SUM) Cell A2: "Option1,Option2,Option3" This would be soooo helpful if possible. Thanks in advance. -- John |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT has never let me down BUT...
hi, JM !
Here's the deal: I know SUMPRODUCT is capable of comparing multiple values against an array by using {}'s like this: Cell A1: SUMPRODUCT((ARRAY={Option1,Option2,Option3})*ARRAY 2SUM) BUT can the criteria range be made to reference a cell value??? Something like this: Cell A1: SUMPRODUCT((ARRAY={A2})*ARRAY2SUM) Cell A2: "Option1,Option2,Option3" This would be soooo helpful if possible. if you need to handle a 'constant-array' through a cell-reference-style in order to maintain flexibility [I guess]... you could use/define a named-formula using the old xl4 macro-function: Evaluate(text) [i.e.] op1: assuming 'A2' with - Option1,Option2,Option3 [NO double quotes] 1) select the appropriate cell where you will use the array reference from 'A2' and go to... 2) [menu] insert / name / define... i.e. myArray 3) use this formula: - =evaluate("{"""&substitute(!a2,",",""",""")&"""}") &rept("",0*now()) adjust for any [semi]absolute/relative cell reference... !$a$2 ... !$a2 ...!a$2 op2: assuming 'A2' with - {"Opton1","Option2","Option3"} [braces and double quotes included] 1) idem 2) idem 3) use this formula: - =evaluate(!a1)&rept("",0*now()) be aware that using xl4 macrofunctions with 'this' cell-reference-style could result in application's 'crash' - if you copy/paste cells using 'the name' across sheets/books [and depending on excel version] :-( hth, hector. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT has never let me down BUT...
Isn't that just
=SUMIF(ARRAY,A1,ARRAY2SUM) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JM" wrote in message m... Here's the deal: I know SUMPRODUCT is capable of comparing multiple values against an array by using {}'s like this: Cell A1: SUMPRODUCT((ARRAY={Option1,Option2,Option3})*ARRAY 2SUM) BUT can the criteria range be made to reference a cell value??? Something like this: Cell A1: SUMPRODUCT((ARRAY={A2})*ARRAY2SUM) Cell A2: "Option1,Option2,Option3" This would be soooo helpful if possible. Thanks in advance. -- John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |