Issues with Excel SUMIF INDEX MATCH

0
votes

I'm working on an Excel 2010 sheet using a series of tables to calculate a variable sum. See the below image for what I'm working with.

Encounter Calculations:

Table 1 Table_Encounters. The column XP Limit is supposed to look at the current value in Difficulty, match appropriate column in Table 2 Table_Players, then add together all numbers in that specific column.

For example, in G3, it should be referencing F3 (Hard), finding Table_Players[Hard], and adding together O3:O10, displaying a result of 8000 (1100+1100+1100+1100+1100+1400+1100).

Every combination of sumif, index, and match I try either returns 0, #ref, or #value. I have no idea what I'm doing wrong, and have been scratching my head over this for the last few hours. I thought I was going to the right direction with this, but I only get #REF so I'm at a loss:

=SUMIFS(INDEX([Difficulty],,MATCH(F$3,Table_Players[#Headers],0)),[Difficulty],$F4)
1
excelexcel-formulamatchexcel-2010sumifs
stop trying combinations. Start with match, get that working, and go from there. – ashleedawg
If you limit the range on one you need to limit the range on the criteria also. – Scott Craner
@ScottCraner - I keep noticing that you have a knack for decoding balderdash. Impressive. – ashleedawg
@ashleedawg Just know the usual suspects. – Scott Craner
@still qualifies as a knack :) – ashleedawg

1 Answers

0
votes

Use this in G3:

=SUM(INDEX($K$3:$Q$10,0,MATCH($F3,$K$2:$Q$2,0)))

Or with your structured references:

=SUM(INDEX(Table_Players[#Data],0,MATCH($F3,Table_Players[#Headers],0)))