title: Sum values using Arrays and INDEX
body: <p>I have the following sample sheet:</p>
  
  <pre><code>1/A B       C   D   E   F   G   H   I   J
  2                                   
  3   Points  8   4   2   1               
  4                                   
  5   Values  1   2   3   4   4   3   1   2
  </code></pre>
  
  <p>I'm trying to sum the 'Points' based upon the array index from the 'Values'.</p>
  
  <p>My expected result from this is: <strong>30</strong></p>
  
  <p>Here is my formula:</p>
  
  <pre><code>{=SUM(INDEX($C$3:$F$3,1,C5:J5))}
  </code></pre>
  
  <p>For some reason though, this only returns the first value of the array, rather than the entire sum.</p>
  
  <p>To clarify, the C# version would be something like:</p>
  
  <pre><code>var points = new int[] { 8, 4, 2, 1 };
  var values = new int[] { 2, 4, 3, 1, 2, 4, 2 };
  
  var result = (from v in values
                select points[v - 1]).Sum(); // -1 as '4' will crash, but in Excel '4' is fine
  </code></pre>
  
  <p><em>Edit: Adding further clarifying example</em></p>
  
  <p>Another example to clarify:</p>
  
  <p>Points is the array. The 'values' represents the index of the array to sum.</p>
  
  <p>The example above is the same as:</p>
  
  <pre><code>=SUM(8, 4, 2, 1, 1, 2, 8, 4)
  </code></pre>