title: Excel: Problems w. INDIRECT, Arrays, and Aggregate Functions (SUM, MAX, etc.)
body: <p><strong>Objective</strong></p>
  <p>I have a Microsoft Excel spreadsheet containing a price list that may change over time (<code>B2:B5</code> in the example).  Separately, I have a budget that too may change over time (<code>D2</code>).  I am attempting to construct a formula for <code>E2</code> to output the number of items that can be purchased with the budget in <code>D2</code>.  Thereafter, I'll attempt to construct formulas to output any change that would be made (<code>F2</code>) and a comma-delimited list of purchasable items (<code>G2</code>).</p>
  <p><em>Note: It unfortunately isn't possible to add an intermediate calculation column to the list, such as a running total.  As such, I'm trying for formulas for single cells (i.e., <code>E2</code>, <code>F2</code>, and <code>G2</code>).</em></p>
  <p><em>Note: I'm using Excel for Mac 2019.</em></p>
  <pre><code>       A         B       C       D         E         F                  G
    +---------+---------+-----+---------+-------+---------+---------------------------+
  1 |  Label  |  Price  |     | Budget  | Items | Change  |          Item(s)          |
    +---------+---------+-----+---------+-------+---------+---------------------------+
  2 | Item #1 | $ 10.00 |     | $ 40.00 |     3 | $  4.50 | Item #1, Item #2, Item #3 |
    +---------+---------+-----+---------+-------+---------+---------------------------+
  3 | Item #2 | $ 20.00 |     |         |       |         |                           |
    +---------+---------+-----+---------+-------+---------+---------------------------+
  4 | Item #3 | $  5.50 |     |         |       |         |                           |
    +---------+---------+-----+---------+-------+---------+---------------------------+
  5 | Item #4 | $ 25.00 |     |         |       |         |                           |
    +---------+---------+-----+---------+-------+---------+---------------------------+
  6 | Item #5 | $ 12.50 |     |         |       |         |                           |
    +---------+---------+-----+---------+-------+---------+---------------------------+
  </code></pre>
  <p>For <code>E2</code>, I've attempted:</p>
  <p><code>{=MAX(N(SUM(INDIRECT(&quot;$B$2:$B$&quot;&amp;ROW($B$2:$B$6)))&lt;=$D2)*ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1)}</code></p>
  <p>Though, the above values and this formula result in an output of <code>-1</code>.</p>
  <p><em>Note: The formula for <code>F2</code> and <code>G2</code> <em>seemingly</em> easily follow <code>E2</code>; e.g. <code>{=$D2-SUM(IF((ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1)&lt;=$E2,$B$2:$B$6,0))}</code> and <code>{=TEXTJOIN(&quot;, &quot;,TRUE,INDIRECT(&quot;$A$2:$A$&quot;&amp;(MIN(ROW($B$2:$B$6))+$E2-1)))}</code> seem to work well, respectively.</em></p>
  <p><strong>Observations</strong></p>
  <ul>
  <li><code>{=&quot;$B$2:$B$&quot;&amp;ROW($B$2:$B$6)}</code> evaluates to <code>{&quot;$B$2:$B$2&quot;;&quot;$B$2:$B$3&quot;;...;&quot;$B$2:$B$6&quot;}</code> <em>(as desired)</em>;</li>
  <li><code>{=INDIRECT(&quot;$B$2:$B$&quot;&amp;ROW($B$2:$B$6))</code> <em>should</em> evaluate to the equivalent of <code>{{$B$2:$B$2},{$B$2:$B$3},...,{$B$2:$B$6}}</code>; though, as a 1x5 multi-cell array formula, evaluates to the equivalent of <code>{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}</code> and, with <code>F9</code> does to <code>{10;#N/A;#N/A;#N/A;12.5}</code>;</li>
  <li><code>{=SUM(INDIRECT(&quot;$B$2:$B$&quot;&amp;ROW($B$2:$B$6)))&lt;=$D2}</code>, as a 1x5 multi-cell array formula, evaluates to the equivalent of <code>{TRUE;TRUE;TRUE;FALSE;FALSE}</code>  <em>(as desired)</em>; though, with <code>F9</code> does to <code>#VALUE!</code>;</li>
  <li><code>{=N(SUM(INDIRECT(&quot;$B$2:$B$&quot;&amp;ROW($B$2:$B$6)))&lt;=$D2)}</code>, as a 1x5 multi-cell array formula, evaluates to the equivalent of <code>1;1;1;0;0</code> <em>(as desired)</em>; though, with <code>F9</code> does again to <code>#VALUE!</code>;</li>
  <li><code>{=N(SUM(INDIRECT(&quot;$B$2:$B$&quot;&amp;ROW($B$2:$B$6)))&lt;=$D2)*ROW($B$2:$B$6)</code>, as as 1x5 multi-cell array formula, evaluates to the equivalent of <code>{2,3,4,0,0}</code> <em>(as desired)</em>; though, with <code>F9</code> does to <code>{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}</code>;</li>
  <li><code>{=N(SUM(INDIRECT(&quot;$B$2:$B$&quot;&amp;ROW($B$2:$B$6)))&lt;=$D2)*ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1}</code>, as a 1x5 multi-cell array formula, evaluates to the equivalent of <code>{1,2,3,-1,-1}</code> <em>(as desired)</em>; though, with <code>F9</code> does again to <code>{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}</code>; and,</li>
  <li><code>{=MAX(N(SUM(INDIRECT(&quot;$B$2:$B$&quot;&amp;ROW($B$2:$B$6)))&lt;=$D2)*ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1)}</code> evaluates to -1</li>
  </ul>
  <p>Interestingly:</p>
  <ul>
  <li>If <code>{=N(SUM(INDIRECT(&quot;$B$2:$B$&quot;&amp;ROW($B$2:$B$6)))&lt;=$D2)*ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1}</code> is placed as the multi-cell array formula in, say, <code>E10:E14</code>, a <code>=MAX($E$10:$E$14)</code> results in <code>3</code> <em>(as desired)</em>.</li>
  </ul>
  <p><strong>Speculation</strong></p>
  <p>At present, I'm speculating that, when entered as a single cell array formula, the <code>INDIRECT</code> is not being assessed to be array producing and/or the <code>SUM</code>, as part of a single cell array formula, is not producing an array result.</p>
  <p>Please assist.  And, thank you in advance.</p>
  <p><strong>Solutions (Thanks to Contributors Below)</strong></p>
  <ul>
  <li>For <code>E2</code>, <code>{=IF($B$2&lt;=$D2,MATCH(1,0/(MMULT(N(ROW($B$2:$B$6)&gt;=TRANSPOSE(ROW($B$2:$B$6))),$B$2:$B$6)&lt;=$D2)),0)}</code> (thank you Jos Woolley);</li>
  <li>For <code>F2</code>, <code>=IF($E2=0,MAX(0,$D2),$D2-SUM($B$2:INDEX($B$2:$B$6,$E2)))</code> (thank you P.b); and,</li>
  <li>For <code>G2</code>, <code>=IF($E2=0,&quot;&quot;,TEXTJOIN(&quot;, &quot;,TRUE,$A$2:INDEX($A$2:$A$6,$E2)))</code> (thank you P.b).</li>
  </ul>