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

MEUserII

Board Regular
Joined
Oct 27, 2017
Messages
65
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 >ABCDEF
11111AAA1055010AAA
21111BBB20
31111BBB30
41111CCC40
51111CCC50
61111CCC60
71111DDD70
81111DDD80
91111DDD90
101111DDD100

<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:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,455
One way would be to enter your array values individually in a range of cells...

A1:F10

1111AAA10AAA550
1111BBB20BBB
1111BBB30CCC
1111CCC40DDD
1111CCC50
1111CCC60
1111DDD70
1111DDD80
1111DDD90
1111DDD100

<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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,113
Messages
5,622,808
Members
415,933
Latest member
Dlegend_79

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top