Sum Index Match?

I'm trying to create sum formula that Indexes and Matches based on criteria and in both columns and rows.

Rows = Category
Column = Date or Key Identifier


Right now I have the formulas as an absolute reference
Example: =SUMIF(MAIN!$C:$C,$B8,MAIN!G:G)
C:C = Match for category
G:G = The exact column the data is in

I would like to make this a an index match because when inserting new columns all the formulas break (60-80 of them). I've search the fourm and found and index match that works but in only finds the first value found, it wont sum them all up.

I've attached an example, but I'm trying to make it as scalable as possible so I can add more months without it breaking.......Hope I explained this okay. THANKS GURUS

maybe this in I7

Please Login or Register  to view this content.

then J7

Please Login or Register  to view this content.

in K7

Please Login or Register  to view this content.

hi toyo613, assume there's typo in your year as Sheet 1 showing 2016 while GROUP Summary showing 2017, you can try below array formula and copy across other cells

D7=SUM(IFERROR((MAIN!$C$5:$C$30=$B7)*INDEX(MAIN!$G$5:$U$30,0,MATCH(D$6,MAIN!$G$5:$U$5,0)),0))

just to confirm did you press ALT+CTRL+ENTER on the array formula?

Could you attach your copy with the proposed formula so that I can have a look of what went wrong?

hi Toyo613, I'm sorry to give you wrong keys combination earlier.
For array formula try hit CTRL+SHIFT+ENTER

To be more precise, in the last sample that uploaded, click on cell D7, press F2 (so that you're in edit mode) then press CTRL+SHIFT+ENTER. Once you verified the outcome you can copy across other cells.

Hi alvin-chung!!

Edit: This actually solved my problem completely!! Solved & Rep Added!