Enhanced sumifs formula???

[jeongs1]

Please see the two tables below. So I have one sheet called DATA SHEET and another called INPUT SHEET. Now, I want to calculate the difference between INVOICED and ACCRUED for each month for each product for their specific advertising type. And would like to have the difference shown up in INPUT SHEET. (ex. in the Input sheet, C3 would be -500; 1500-2000).

I have been just manually doing SUMIFS using product and adv type. But is there a way I can add the month to the formula, so that all I have to do is input the data for future months and the formula would pick it up in the input sheet??

Thank you!!!


DATA SHEET #1

A	B	C	D	E	F	G	H
		ACCRUED	INVOICED	ACCRUED	INVOICED	ACCRUED	INVOICED
PRODUCT	ADV. TYPE	JAN-17	JAN-17	FEB-17	FEB-17	MAR-17	MAR-17
GUM	TV	2000	1500	1000	1000	1500	2000
GUM	RADIO	1500	1300	1300	1315	1450	1400
ICE CREAM	TV	2000	2300	2500	2600	2500	2300
ICE CREAM	RADIO	1200	1250	1200	1150	1100	1000

<thead>
</thead><tbody>
</tbody>

INPUT SHEET #2

A	B	C	D	E
PRODUCT	ADV. TYPE	JAN-17	FEB-17	MAR-17
GUM	TV
GUM	RADIO
ICE CREAM	TV
ICE CREAM	RADIO

<thead>
</thead><tbody>
</tbody>

 

[Jonmo1]

Well, manually, I've been doing SUMIFS using just the product and the advertising type.

I know, I read that in your original post.

Can you post an Example of that formula?

 

[dreid1011]

All the cells are formatted the same way.

That doesn't answer the question.... and doesn't help to solve the issue. Specifics, and humor us, post exactly the formula you have been using and copying across manually please?

 

[jeongs1]

My apologies...

=SUMIFS('DATA SHEET'!D:D,'DATA SHEET'!$B:$B,'INPUT SHEET'!$B2,'DATA SHEET'!$A:$A,'INPUT SHEET'!$A2)-SUMIFS('DATA SHEET'!C:C,'DATA SHEET'!$B:$B,'INPUT SHEET'!$B2,'DATA SHEET'!$A:$A,'INPUT SHEET'!$A2)

This is the formula that I've been using. And for Feb, I would simply just change the sum range according the the month.

 

[Jonmo1]

Try

=SUMIFS(INDEX('DATA SHEET'!$D:$H,0,MATCH('INPUT SHEET'!C$1,'DATA SHEET'!$C$2:$G$2,0)),'DATA SHEET'!$B:$B,'INPUT SHEET'!$B2,'DATA SHEET'!$A:$A,'INPUT SHEET'!$A2)-SUMIFS(INDEX('DATA SHEET'!$C:$G,0,MATCH('INPUT SHEET'!C$1,'DATA SHEET'!$C$2:$G$2,0)),'DATA SHEET'!$B:$B,'INPUT SHEET'!$B2,'DATA SHEET'!$A:$A,'INPUT SHEET'!$A2)

 

[jeongs1]

IT WORKED!! but does the array for INDEX have to start at column D and column C? They both can't be starting at C??? Just curious...

Try

=SUMIFS(INDEX('DATA SHEET'!$D:$H,0,MATCH('INPUT SHEET'!C$1,'DATA SHEET'!$C$2:$G$2,0)),'DATA SHEET'!$B:$B,'INPUT SHEET'!$B2,'DATA SHEET'!$A:$A,'INPUT SHEET'!$A2)-SUMIFS(INDEX('DATA SHEET'!$C:$G,0,MATCH('INPUT SHEET'!C$1,'DATA SHEET'!$C$2:$G$2,0)),'DATA SHEET'!$B:$B,'INPUT SHEET'!$B2,'DATA SHEET'!$A:$A,'INPUT SHEET'!$A2)

 

[Jonmo1]

You can start both at C, but you'd have ADD 1 to the match function in the one that I started in D.

 

[jeongs1]

Oh I see... Thank you so much for the help!!!

 

[Jonmo1]

You're welcome

 

[jeongs1]

Just one more thing. Sorry again. But if we have two sponsors, see below, Now, using the formula you told me, I was able to do it for Mr.J without any issue. Now, I tried to do the same thing for Mr.K, but it's returning the result as #N/A. Now, I can't separate the DATA SHEET into two, since that's how my team receives it...



DATA SHEET

A	B	C	D	E	F	G	H	I	J
		ACCRUED	ACCRUED	INVOICED	INVOICED	ACCRUED	ACCRUED	INVOICED	INVOICED
PRODUCT	ADV. TYPE	JAN-17	JAN-17	JAN-17	JAN-17	FEB-17	FEB-17	FEB-17	FEB-17
GUM	TV	MR. J 2000	MR.K 1500	MR.J 1000	MR.K 1000	MR.J 1500	MR.K 2000
GUM	RADIO	1500	1300	1300	1315	1450	1400
ICE CREAM	TV	2000	2300	2500	2600	2500	2300
ICE CREAM	RADIO	1200	1250	1200	1150	1100	1000

<tbody>
</tbody>

INPUT SHEET -MR.J

A	B	C	D	E
PRODUCT	ADV. TYPE	JAN-17	FEB-17	MAR-17
GUM	TV
GUM	RADIO
ICE CREAM	TV
ICE CREAM	RADIO

<tbody>
</tbody>

INPUT SHEET -MR.K

A	B	C	D	E
PRODUCT	ADV. TYPE	JAN-17	FEB-17	MAR-17
GUM	TV
GUM	RADIO
ICE CREAM	TV
ICE CREAM	RADIO

<tbody>
</tbody>

You're welcome

 

[Jonmo1]

Sorry I don't understand what you're trying to add to it.
And something went wrong with your Input Sheet formatting on the forum.