# SUM based on an Array Criteria That is In A Different Cell

#### ajones

##### Board Regular
I have a SUM IF formula that looks like this (there are additional sumifs criteria but removed them to keep example short)

=SUMIFS('Data Pull'!\$K\$12:\$K\$24363,'Data Pull'!\$C\$12:\$C\$24363, \$D12)

Where D12 contains 51*

I later wanted to to do the same thing with multiple accounts
Say D12 contains an array of accounts or other comma separated lists.

I knew i needed to operate on an array (but in this case no CTRL+SHIFT+ENTER)
=SUM(SUMIFS('Data Pull'!\$K\$12:\$K\$24363,'Data Pull'!\$C\$12:\$C\$24363, {"51*","55*","57*"} ))
This worked but I would really like to put that array in D12 so D12 = {51*,55*,57*}
and the SUM/SUMIFs referenced the cell.
=SUM(SUMIFS('Data Pull'!\$K\$12:\$K\$24363,'Data Pull'!\$C\$12:\$C\$24363, D12 ))
i have tired changing how the list/array in D12 was representing and variations of this formula, but so far nothing, and tried to change things based on what I saw with Evaluate Formula, but so far nothing has worked.

How can I reference an array instead of a single value?

Any thoughts?

Alan

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### oldbrewer

##### Well-known Member
 "51*","55*","57*" " , enter 51* your criteria here 55* 57* {"51*","55*","57*"} )) "51*","55*","57*" =I1&I3&I1&J1&I1&I4&I1&J1&I1&I5&I1 in A1 '=I16 =SUMIFS('Data Pull'!\$K\$12:\$K\$24363,'Data Pull'!\$C\$12:\$C\$24363, indirect(A1)) not tested but A1 is meant to reference your list eg 51 55 57

<colgroup><col span="15"></colgroup><tbody>
</tbody>

#### ajones

##### Board Regular
thanks for the reply... I am a little confused by your example, but from what i see .... my goal is to keep 51* 55* 57* all in the same cell. As the number of items to do a "sumif" on may vary.

#### oldbrewer

##### Well-known Member
if you wanted 59 as well just type it in the cell below 57

the concatenate "formula" can be made to add blanks if no number in say I6

#### Eric W

##### MrExcel MVP
There's no easy way to do what you ask. Consider:

ABCDEFGH
15100125505
252002
353003
454004
555005
656006
7
8
9
10
11
1252*52*{"52*","53*"}52*53*
1353*
14

</tbody>
Sheet2

Worksheet Formulas
CellFormula
D1=SUMIFS(B1:B6,A1:A6,D12)
E1=SUM(SUMIFS(B1:B6,A1:A6,{"52*","53*"}))

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
F1{=SUM(SUMIFS(B1:B6,A1:A6,F12:F13))}
G1{=SUM(SUMIFS(B1:B6,A1:A6,G12))}
H1{=SUM(SUMIFS(B1:B6,A1:A6,MID(H12,ROW(INDIRECT("1:"&LEN(H12)/3))*3-2,3)))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Your basic formula is in D1. The way to check for multiple matches is in E1, as you've found. If you want to make it so that you can change the criteria without changing the formula, you would use the array formula in F1. What you seem to be asking is how you can do the formula in G1, where all the criteria are in one cell. As you can see, it does not work. Excel will not let you create an array in a cell simply by putting {} around it.

You could use something like the formula in H1. If all your criteria are 3 characters, you can string them all together, and use MID to pull out each piece. But this doesn't scale really well.

Also, in case you have a SUMIFS (or other xxxIFS function), you can only have 2 arrays as criteria. If you add another array, one must be horizontal, and the other vertical. More than that would require specialized, situation-specific formulas, or more likely a UDF.

Hope this helps.

Replies
1
Views
141
Replies
3
Views
39
Replies
0
Views
39
Replies
1
Views
145
Replies
9
Views
117

1,141,204
Messages
5,704,951
Members
421,372
Latest member
Jamie11

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