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

#### MEUserII

##### Board Regular
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:
 ROW V / COLUMN > A B C D E F 1 1111 AAA 10 550 10 AAA 2 1111 BBB 20 3 1111 BBB 30 4 1111 CCC 40 5 1111 CCC 50 6 1111 CCC 60 7 1111 DDD 70 8 1111 DDD 80 9 1111 DDD 90 10 1111 DDD 100

<tbody>
</tbody>

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?

Last edited:

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Domenic

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

A1:F10

 1111 AAA 10 AAA 550 1111 BBB 20 BBB 1111 BBB 30 CCC 1111 CCC 40 DDD 1111 CCC 50 1111 CCC 60 1111 DDD 70 1111 DDD 80 1111 DDD 90 1111 DDD 100

<tbody>
</tbody>

Then you could use the following formula...

Code:
``=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...

Code:
``````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...

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

Hope this helps!

Replies
7
Views
403
Replies
3
Views
147
Replies
5
Views
581
Replies
6
Views
165
Replies
1
Views
76

1,126,966
Messages
5,621,874
Members
415,862
Latest member
nascaline

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back