SUM(SUMIFS()) Formula with Array Stored in Another Cell as Part of the Multiple Criteria?

[MEUserII]

I am working in Excel 2016 with a SUM(SUMIFS()) formula that I am trying to use with an array as part of the multiple criteria where that array is stored in another cell. Specifically, while I can get this SUM(SUMIFS()) formula to calculate correctly when that array is directly written out as part of the multiple criteria; I cannot get this SUM(SUMIFS()) formula to calculate correctly when that array is stored in another cell and that cell is referenced as part of the multiple criteria?

For example, consider the following table:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD="align: center"]ROW V / COLUMN >[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]550[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]AAA[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]BBB[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]BBB[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]CCC[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]CCC[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]CCC[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]DDD[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]DDD[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]DDD[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]DDD[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Where the following formulas are stored in the cell references listed.
D1: =SUM(SUMIFS( ($C$1:$C$10), ($A$1:$A$10), ($A1), ($B$1:$B$10), ({"AAA","BBB","CCC","DDD"} ) ) )
E1: =SUM(SUMIFS( ($C$1:$C$10), ($A$1:$A$10), ($A1), ($B$1:$B$10), (F1) ) )
F1: ={"AAA", "BBB", "CCC", "DDD"}

Specifically, when the array stored in F1 is manually typed out in to the formula in D1 as part of the multiple criteria it correctly calculates the sum as 550. However, when that same formula is entered in to E1 except the array is instead referenced as a cell reference, F1, the sum is incorrectly calculated as 10.

How would I be able to correct the formula in E1 to correctly calculate the SUM(SUMIFS()) with the array stored in F1 referenced as a cell reference, F1?

 

[Domenic]

One way would be to enter your array values individually in a range of cells...

A1:F10

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1111[/TD]
[TD="class: xl65, width: 64"]AAA[/TD]
[TD="class: xl65, width: 64, align: right"]10[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]AAA[/TD]
[TD="class: xl65, width: 64, align: right"]550[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1111[/TD]
[TD="class: xl65"]BBB[/TD]
[TD="class: xl65, align: right"]20[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]BBB[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1111[/TD]
[TD="class: xl65"]BBB[/TD]
[TD="class: xl65, align: right"]30[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]CCC[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1111[/TD]
[TD="class: xl65"]CCC[/TD]
[TD="class: xl65, align: right"]40[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]DDD[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1111[/TD]
[TD="class: xl65"]CCC[/TD]
[TD="class: xl65, align: right"]50[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1111[/TD]
[TD="class: xl65"]CCC[/TD]
[TD="class: xl65, align: right"]60[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1111[/TD]
[TD="class: xl65"]DDD[/TD]
[TD="class: xl65, align: right"]70[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1111[/TD]
[TD="class: xl65"]DDD[/TD]
[TD="class: xl65, align: right"]80[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1111[/TD]
[TD="class: xl65"]DDD[/TD]
[TD="class: xl65, align: right"]90[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1111[/TD]
[TD="class: xl65"]DDD[/TD]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

Then you could use the following formula...

=SUMPRODUCT(SUMIFS($C$1:$C$10,$A$1:$A$10,$A1,$B$1:$B$10,E1:E4 ) )

Another way would be to use a defined name to refer to your array...

1) Ribbon >> Formulas >> Defined Names >> Define Name

2) Name:  Array

3) Refers to:  =EVALUATE("{""AAA"",""BBB"",""CCC"",""DDD""}")

4) Click OK

Then you can use the following formula...

=SUMPRODUCT(SUMIFS($C$1:$C$10,$A$1:$A$10,$A1,$B$1:$B$10,Array))

Hope this helps!