# Finding the SUM of an array where the size of the array is dependent on a cell's contents

Here is a simplified example of what I want:

sheet 1 sheet 2 Essentially Column C finds the row on Sheet 2 with the same Item as Sheet 1 (Columns A) and sums up the Upgrade cost for the current Upgrade level.

So an Upgrade level of 3 finds the sum of Sheet 2, B:D where the Items are the same, where an Upgrade level of 5 will find the sum of B:F.

Add the following line of code into `Sheet1!C2`:

``````=SUM(OFFSET(INDIRECT("Sheet2!B"&MATCH(A2;Sheet2!A:A,0)),0,0,1,B2))
``````

The formula explained from the inside to the outside:

1. `MATCH` in an equivalent of the `VLOOKUP`. It will return a relative position as an integer, rather than a specific value.
2. `INDIRECT` combines a `STRING` with a value into a reference (`B2`).
3. `OFFSET` is a not commonly used formula, but quite powerful. It sets the range of the `SUM`.
4. `SUM` will sum the outcome.

See the example file I created: Finding the SUM of an Array

• Thank you Jacob, this seems to work. However, it inexplicably does not work sometimes? I have expanded my example spreadsheet to illustrate the problem: docs.google.com/spreadsheet/… – Rushmik Apr 1 '13 at 3:45
• @Rushmik I found out what was causing the mayhem. In the `MATCH` formula, I've added `,0`. This solves the problem. Sorry for that... – Jacob Jan Tuinstra Apr 1 '13 at 8:49
• No problem at all! Everything works perfectly now - thanks. – Rushmik Apr 1 '13 at 11:21

For the first part of you problem you can use VLOOKUP().

You can use VLOOKUP to search the second tab range for the row with the rocket launcher in it. Then you can specify which number you want to get returned by stating the index (index 1 is first column in the range).

The problem of you current Sheet2 is that the numbers aren't cumulative. If you make it cumulative, so values 10, 40, 80, 180, 680, you can use the (upgrade number)+1 as your index.

So in the upgrade cost cell C2 you would get something like:

``````=VLOOKUP(A2;Sheet2!A:F;A3+1;False)
``````
• This is very helpful, yet I need to keep the Sheet 2 values as they are (not cumulative) so they can be viewed and edited more easily. Is there a way to turn two `VLOOKUP()`s into a range to put within a `SUM()`? Such as `SUM(VLOOKUP()&":"&VLOOKUP())`? – Rushmik Mar 31 '13 at 4:54