Dynamic Array Constant

[bkjohn2016]

Is it possible to reference another cell to use as an array constant?

Let's say in table A I have the following data:

	A	B
1	Type Code	Amount
2	1	10
3	2	20
4	3	30
5	4	40
6	5	50

<tbody>
</tbody>

And I'd like the the amount aggregated by the type code in table B:

	A	B	C
10	Type Desc	Type Code	Amt
11	A	1	10
12	B	2	20
13	C	1,2	30
14	D	5	50
15	E	4	40

<tbody>
</tbody>

I use the following formulation to get the aggregations in column C of table B:

=SUMPRODUCT(--(A$1:$A$6=$B11),
$B$2:$B$6)

And for C13 I can use the following to get the desired result of 30:

=SUMPRODUCT(--($A1:$A$6={1,2})*
$B$1:$B$6)

Is it possible to create a formulation that would use cell B13 to create a dynamic array constant?

 

[lrobbo314]

I have 2 solutions. First, a Power Query solution, which I think is more dynamic and better in general.

Add Table1, add Table2, then here are the Table2 transformations:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Type Code", type text}}, "en-US"), "Type Code", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Type Code.1", "Type Code.2", "Type Code.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Type Code.1", Int64.Type}, {"Type Code.2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Type Desc"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}})
in
    #"Changed Type1"

Merge Table1 and Table2 as new query, then transformations:

let
    Source = Table.NestedJoin(Table1,{"Type Code"},Table2,{"Value"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Type Desc", "Value"}, {"Type Desc", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Type Code", "Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Type Desc", "Amount"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,"",Replacer.ReplaceValue,{"Type Desc"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Type Desc] <> null and [Type Desc] <> ""),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Type Desc"}, {{"Total", each List.Sum([Amount]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Type Desc", Order.Ascending}})
in
    #"Sorted Rows"

And here is the formula solution. It is an array formula so Ctrl+Shift+Enter when entering the formula:

=SUM(IFERROR(LOOKUP(MID(SUBSTITUTE(B13,",",0),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B13,",",0)))),1)+0,$A$2:$A$6,$B$2:$B$6),0))

 

[Domenic]

Here's another method. This one involves defining a name, and then using it in the formula. Since the reference for the defined name is a relative reference, you'll need to first select cell C11. Then, with the cell selected, define the following name (Ribbon >> Formulas >> Defined Names >> Define Name)...

Name:  MyArray

Refers to:  =EVALUATE("{"&'Sheet1'!$B11&"}")

Click OK

Then try...

C11, copied down:

=SUMPRODUCT(SUMIF($A$2:$A$6,MyArray,$B$2:$B$6))

Note, though, you'll need to ensure that the values in Column B in Table B do not start or end with a comma.

Hope this helps!

 

[bkjohn2016]

Ha! Evaluate in a named range...that's perfect! I did not know that EVALUATE could be used outisde VBA. Irobbos suggestion was also considered but I failed to mention that my client has acute excel anxiety and is intimidated by advanced techniques. Looks like I've learned something new...thank you both so much!