Sum values using Arrays and INDEX

Question

I have the following sample sheet:

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

I'm trying to sum the 'Points' based upon the array index from the 'Values'.

My expected result from this is: 30

Here is my formula:

{=SUM(INDEX($C$3:$F$3,1,C5:J5))}

For some reason though, this only returns the first value of the array, rather than the entire sum.

To clarify, the C# version would be something like:

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

Edit: Adding further clarifying example

Another example to clarify:

Points is the array. The 'values' represents the index of the array to sum.

The example above is the same as:

=SUM(8, 4, 2, 1, 1, 2, 8, 4)

As sum one that does not program in C#, could you spell out the number that would get added together and the result of such addition? — Scott Craner
– Scott Craner, Commented Jul 13, 2016 at 4:11

Axel Richter · Accepted Answer · 2016-07-13 07:32:03Z

4

INDEX will never take its row or column parameters from arrays and then perform multiple times within one array formula contained in one cell. For this OFFSET will be needed.

Either

{=SUM(N(OFFSET($C$3,,C5:J5-1)))}

as an array formula.

Or

=SUMPRODUCT(N(OFFSET($C$3,,C5:J5-1)))

as an implicit array formula without the need for [Ctrl]+[Shift]+[Enter].

edited Jul 13, 2016 at 7:32

answered Jul 13, 2016 at 5:18

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user4039065 Over a year ago

If you could take the time to fuel my own curiosity, what results are you receiving from the sample data? I was waiting to see what results the OP expected. wadr, it is rare that a non-volatile INDEX function cannot replace a volatile OFFSET function but I was hoping to see what the OP expected as a result as it is not clear (to me) from the sample data, code or formula in the question.

Axel Richter Over a year ago

The result is the sum of 8, 4, 2, 1, 1, 2, 8, 4 which is 30 and are the values from C3:F3 taken one by one by column indices from C5:J5. And as of my experience INDEX will never take its row or column parameters from arrays.

Axel Richter Over a year ago

Btw.: The #C example has other indices. There the result will be 24 = sum(4, 1, 2, 8, 4, 1, 4)

Collectives™ on Stack Overflow

Sum values using Arrays and INDEX

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related