Iterative formula for summing multiple values with multiple conditions

0
votes

I'm trying to write a formula that does the following:

  • References a cell to determine which tab/sheet to use
  • Matches a column and row for each criteria and returns the value
  • Sums the matched values

The closest I get is an index-match-match formula however there are several limitations including:

  • If I add a new Column, then the formula won't pick up on it.

In the photo below is an example of what I'm after and the formula I'm using for now. The Criteria section A1:K12 is on a separate sheet.

On my master sheet is the Aircraft section A16:K17 with the columns and rows that form each criteria that I need to sum from the Criteria sheet. Row 1, 2, 3, etc are difference install options.

enter image description here

1
google-sheetsgoogle-sheets-formula
Excel or Google sheets? Do you know which you are using? - Solar Mike
Please post the formula you've tried so far. - Spencer Barnes
Indirect() can be used to include new columns - check on here. - Solar Mike
share a copy/sample of your sheet - player0

1 Answers

1
votes

Result in a separate sheet

*Values are modified to easy verification.

Assume you put the sheetname and ranges in a way as shown in the first image.

=SUM(ArrayFormula(IFNA(VLOOKUP(INDIRECT(B1&"!"&B5),INDIRECT(B1&"!"&B3),MATCH(INDIRECT(B1&"!"&B4),INDIRECT(B1&"!"&B2),0),FALSE),0)))

Updates:

Rules on the ranges:

  • Criteria "Column" range should be the same as the starting row of Criteria: section (2:2 & A2:K12)
  • Criteria: section should starts from first column (A2:K12)

*You can add any columns (blank columns, or non-blank & non-criteria columns) at any position, if Column A is Row 1, 2, 3,....

This also applies to rows, you could insert any blank or unrelated rows insides the Criteria: section.

By the way, may be 2:12 is better for Criteria: section is you are considering to add columns.

*Modified values