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

ajones

Board Regular
Joined
Oct 26, 2002
Messages
104
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*
Thus it only sums accounts that start with 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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
"51*","55*","57*"",
enter51*
your criteria here55*
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
Joined
Oct 26, 2002
Messages
104
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
Joined
Apr 11, 2010
Messages
11,005
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
Joined
Aug 18, 2015
Messages
11,123
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

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

<thead>
</thead><tbody>
</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)))}

<thead>
</thead><tbody>
</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.
 

Forum statistics

Threads
1,136,780
Messages
5,677,688
Members
419,712
Latest member
LearningCR

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