0

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

enter image description here

  • 1
    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
0

enter image description here

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))),"")

Adjust cell references in the formula as needed.

| improve this answer | |
  • Glad to help you @freakyholic keep asking ,, ☺ – Rajesh S Jul 27 '19 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.