Index match - formula moving columns and rows

[mtn629]

Hi guys,

First time poster, long time excel user!

Briefly, i am trying to automate a data import to show the total sales per sales centre per month using a combination of SUM IF and INDEX-MATCH.

My issue is that as i copy my formula across to the next column, the index-match formula seems to move down a row. Each time i copy the formula to the next column it moves down again and sums the wrong data. I have made sure all formulas are locked appropriately but cannot figure out why it is doing this. 

Please note the bordered cells are the figures the formula is picking up, when it should be returning the sum of the sales centre for the particular month.

Screenshot with formulas:

Would really appreciate some help as i cannot find a solution.

Thanks!

[#2 · Accepted solution]

@mtn629 

You may try something like this...

=SUMIF($C$5:$C$18,$A24,INDEX($D$5:$O$18,,MATCH(B$23,$D$3:$O$3,0)))

[#3]

@Subodh_Tiwari_sktneer 

Excellent, that worked!

May i ask what i was doing wrong and what the double comma between index and match does? I have never seen that before?

Thank you once again!

[#4]

@mtn629 

Please pay attention to the array you are passing inside the Index function, the first argument. I have used a two-dimensional array which is the key of the formula.

If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.