Google Sheets – SUM Array Based on Cell Content

google sheets

Here is a simplified example of what I want:

sheet 1
enter image description here

sheet 2
enter image description here

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.

Best Answer

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

Related Topic