2-way lookup for a range of values with SUMIF ???

[waterconsultant]

Sorry for this trivial problem, but it has me stumped. Trying to SUM a range of values in the range C2:G4 depending on 2 criteria. a) the test value of the row in Row 5. In this EXAMPLE the target value is "XX", and any matching column occurrences of XX need to be summed across the row. b) There are also 3 possible scenarios, High, Medium, Low. Say the scenario is 'Low', and therefore the formula below returns 12+14 = 26, BUT I have 'hard-coded' the row in C4:G4. What I can't fix is how I introduce a lookup/match function so that I can change the row number (i.e. 2, 3 or 4). Any solution is fine as long as I keep some form of offset (reason being that this data block is repeated across the sheet, and therefore the offset position (currently A1) also moves.

=SUMIF(OFFSET(A1,4,2,1,5),"XX",C4:G4)

(sorry folks, every time I carefully format the data, preview completely trashes the layout. Don;t know how to fix that


A B C D E F G
1

2 High 20 50 30 10

3 Medium 15 10 11 25

4 Low 12 14

5 DD XX YY BB XX[TABLE="width: 549"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any support very gratefully appreciated.
Brian F

 

[Oaktree]

Here's one way:

=SUMIF(INDEX(OFFSET(A1,1,2,4,5),4,0),"XX",INDEX(OFFSET(A1,1,2,4,5),3,0))

OFFSET(A1,1,2,4,5) will reference the whole C2:G5 range. By using 0 as the column number within the INDEX function, you're able to reference a full row within the array.

You'd just need to replace the 3 near the end with 2 for Medium or 1 for High. Or, have an input cell for Low/Medium/High and use a MATCH formula to determine that number so it's not hardcoded in the formula.