title: ROW() function behaves differently inside SUM() and SUMPRODUCT()
body: <p><strong>Problem definition:</strong></p>
  
  <p>Enter any number in cell <code>A1</code>. Now try the following formulae anywhere on first row.</p>
  
  <pre><code>=SUM(INDIRECT("A"&amp;ROW()))
  </code></pre>
  
  <p>and</p>
  
  <pre><code>=SUMPRODUCT(INDIRECT("A"&amp;ROW()))
  </code></pre>
  
  <p>The first formula evaluates, the second one gives a #VALUE error.
  This is caused by the <code>ROW()</code> function behaving differently inside <code>SUMPRODUCT()</code>.</p>
  
  <p>In the first formula, <code>ROW()</code> returns <code>1</code>. In the second formula, row returns <code>{1}</code> (array of one length), even though the formula has not been entered as a CSE formula.</p>
  
  <p>Why does this happen?</p>
  
  <p><strong>Background</strong></p>
  
  <p>I need to evaluate a formula of the type </p>
  
  <pre><code>=SUMPRODUCT(INDIRECT(*range formed by concatenation and using ROW()*)&gt;1)
  </code></pre>
  
  <p>This is working out to an error. As a workaround to this issue, I now calculate <code>ROW()</code> in another cell (in the same row, obviously) and concatenate that inside my <code>INDIRECT()</code>. Alternately, I also have tried encapsulating it inside a sum function, like <code>SUM(ROW())</code>, and that works as well.</p>
  
  <p>I would sure appreciate it if someone could explain (or point me to a resource that can explain) why <code>ROW()</code> returns an array inside <code>SUMPRODUCT()</code> without being CSE entered.</p>