SUMIFS/INDEX/MATCH Help!!!

[jeongs1]

So, I'm doing a true-up calculations and need the amount to be summed up by their location and then subtract from what was actualized to what was projected. Location is done by doing the "=RIGHT(A3,2)". And as you can see from the DATA SHEET, not all the location and media type that was projected have their actualized amount (ex, in January, we have projected amount for TV-LV, but not actualized amount). Also, from month to month, the location varies as well. (ex, January have SF but not in February).

I've been doing

SUMIFS(INDEX('DATA'!$A:$J,0,MATCH('INPUT'!B$1,'DATA'!$A$1:$J$1,0)+5),'DATA'!$E:$E,'INPUT'!$A2)-SUMIFS(INDEX('DATA'!$A:$J,0,MATCH('INPUT'!B$1,'DATA'!$A$1:$J$1,0)+2),'DATA'!$B:$B,'INPUT'!$A2)

The formula will be inserted into "B3" of the INPUT sheet

And then for the next month, I'd manually drag the E:E and B:B column to match the month - drag to M:M and H:H for February...
I was wondering if there are any way that I didn't have to manually change the columns for the location. So that all I have to do is input the data into the DATA sheet, and the formula would automatically sum the corresponding month's, actualized amount for specified location and subtract it from that of projected amount.


DATA
[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[TH="align: center"]H[/TH]
[TH="align: center"]I[/TH]
[TH="align: center"]J[/TH]
[/TR]
[TR]
[TD="align: center"]JAN-17[/TD]
[TD="align: center"]JAN-17[/TD]
[TD="align: center"]JAN-17[/TD]
[TD="align: center"]JAN-17[/TD]
[TD="align: center"]JAN-17[/TD]
[TD="align: center"]JAN-17[/TD]
[TD="align: center"]FEB-17[/TD]
[TD="align: center"]FEB-17[/TD]
[TD="align: center"]FEB-17[/TD]
[TD="align: center"]FEB-17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]LOCATION[/TD]
[TD="align: center"]PROJECTED[/TD]
[TD="align: center"][/TD]
[TD="align: center"]LOCATION[/TD]
[TD="align: center"]ACTUALIZED[/TD]
[TD="align: center"][/TD]
[TD="align: center"]LOCATION[/TD]
[TD="align: center"]PROJECTED[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]TV-SF[/TD]
[TD="align: center"]SF[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]TV-SF[/TD]
[TD="align: center"]SF[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]Radio-LV[/TD]
[TD="align: center"]LV[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Radio-SF[/TD]
[TD="align: center"]SF[/TD]
[TD="align: center"]1500[/TD]
[TD="align: center"]Radio-SF[/TD]
[TD="align: center"]SF[/TD]
[TD="align: center"]1315[/TD]
[TD="align: center"]TV-LV[/TD]
[TD="align: center"]LV[/TD]
[TD="align: center"]1400[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]TV-LV[/TD]
[TD="align: center"]LV[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]Radio-LV[/TD]
[TD="align: center"]LV[/TD]
[TD="align: center"]2600[/TD]
[TD="align: center"]Online-LV[/TD]
[TD="align: center"]LV[/TD]
[TD="align: center"]2300[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Radio-LV[/TD]
[TD="align: center"]LV[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]Online-LV[/TD]
[TD="align: center"]LV[/TD]
[TD="align: center"]1150[/TD]
[TD="align: center"]Radio-LA[/TD]
[TD="align: center"]LA[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]



INPUT[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"][/TH]
[TH="align: center"][/TH]
[TH="align: center"][/TH]
[TH="align: center"][/TH]
[TH="align: center"][/TH]
[/TR]
[TR]
[TD="align: center"]LOCATION[/TD]
[TD="align: center"]JAN-17[/TD]
[TD="align: center"]FEB-17[/TD]
[TD="align: center"]MAR-17[/TD]
[TD="align: center"]APR-17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]LA[/TD]
[TD="align: center"]FORMULA HERE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]LV[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]SF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Chrisdontm]

I believe you want your formula in cell: B2

Try this and see if it is what you are wanting:

=SUMIF(OFFSET(Data!$A$1,2,((COLUMN()-1)*6)-2,COUNTA(Data!$B:$B),1),$A2,OFFSET(Data!$A$1,2,((COLUMN()-1)*6)-1,COUNTA(Data!$B:$B),1))-SUMIF(OFFSET(Data!$A$1,2,((COLUMN()-1)*6)-5,COUNTA(Data!$B:$B),1),$A2,OFFSET(Data!$A$1,2,((COLUMN()-1)*6)-4,COUNTA(Data!$B:$B),1))

 

[jeongs1]

It doesn't work It wants me to add additional parenthesis and I don't know where to put it because I've used the exact formula you told me.

I believe you want your formula in cell: B2

Try this and see if it is what you are wanting:

=SUMIF(OFFSET(Data!$A$1,2,((COLUMN()-1)*6)-2,COUNTA(Data!$B:$B),1),$A2,OFFSET(Data!$A$1,2,((COLUMN()-1)*6)-1,COUNTA(Data!$B:$B),1))-SUMIF(OFFSET(Data!$A$1,2,((COLUMN()-1)*6)-5,COUNTA(Data!$B:$B),1),$A2,OFFSET(Data!$A$1,2,((COLUMN()-1)*6)-4,COUNTA(Data!$B:$B),1))

 

[Chrisdontm]

I made sure that I posted the complete formula, it works fine...???

How about if you copy the formula off of your "Input" worksheet in cell B2, and post it as a reply to me...?

 

[jeongs1]

I have it written up there, but here you go

SUMIFS(INDEX('DATA'!$A:$J,0,MATCH('INPUT'!B$1,'DATA'!$A$1:$J$1,0)+5),'DATA'!$E:$E,'INPUT'!$A2)-SUMIFS(INDEX('DATA'!$A:$J,0,MATCH('INPUT'!B$1,'DATA'!$A$1:$J$1,0)+2),'DATA'!$B:$B,'INPUT'!$A2)

And for Feb, I'd change "E:E" and "B:B" to "M:M" and "H:H".
Thanks

I made sure that I posted the complete formula, it works fine...???

How about if you copy the formula off of your "Input" worksheet in cell B2, and post it as a reply to me...?

 

[Chrisdontm]

I was meaning for you to copy what you put into B2 from my formula.
You say that it is not working. I was making sure you correctly copied my formula.
You just posted your formula again.

I want to see what you are saying is not working, because my formula is working.

As for the different columns to reference for the different months, shouldn't it be columns H:H and L:L instead of B:B and E:E?
If you use M:M then you are adding in an extra column from what you have for January....???