# Replacement for 'sum_range' in SUMIFS formula that can match automatically with source data header

Need your help, through SUMIFS formula, I need the result to be like the one in D5 column. From the formula box in picture, we can see that I wanted to know the ad cost between July 6 to July 8 which data is referred from table range of G4:K7 and the date-range is referred from cells range C1:C2

I need the formula of 'H5:H7' (in yellow) like stated in the formula box in below picture to be replaced, in order to be automatically detected (as far as I know we have to use INDEX & MATCH formula to replace the 'H5:H7').

So then, say, when I make the formula for D6 column, I don't need to change H5:H7 (in yellow at formula box below) into I5:I7 manually anymore to refer the 'Revenue' in I5:I7.

I have tried something like:

(index((H5:K7),,match(C5,(H4:K4),0) to replace H5:H7 in yellow below but it didn't work. What should be the replacement formula for H5:H7 instead of the one I've tried already in above?

thanks • Perhaps you're just missing some absolute referencing? =SUMIFS(INDEX(H\$5:K\$7,,MATCH(C5,H\$4:K\$4,0)),G\$5:G\$7,">="&C\$1,G\$5:G\$7,"<="&C\$2) in D5 and copied down should give you what you want. – XOR LX Sep 8 '18 at 19:58
• Do you want to get the result as shown? imgur.com/a/hBoEqPr Try this formula: =SUM(INDIRECT(SUBSTITUTE(LEFT(ADDRESS(1,MATCH(C5,\$4:\$4,0)),3),"\$","")&SUMPRODUCT((\$G\$4:\$K\$7=\$C\$1)*ROW(\$4:\$7))&":"&SUBSTITUTE(LEFT(ADDRESS(1,MATCH(C5,\$4:\$4,0)),3),"\$","")&SUMPRODUCT((\$G\$4:\$K\$7=\$C\$2)*ROW(\$4:\$7)))) – Lee Sep 10 '18 at 9:45 Write this Array in Cell `B67` finish with `Ctrl+Shift+Enter` and fill down.
``````=IF(AND(\$C\$68:\$C\$70>=\$A\$64,\$C\$68:\$C\$70<=\$A\$65),SUM(INDEX(\$D\$68:\$G\$70,,MATCH(A67,\$D\$67:\$G\$67,0))),"")