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>

 

[Scott T]

Try in C2 this is an array formula and must be entered with CONTROL+SHIFT+ENTER. If done correctly excel will put {} around the formula. Copy across and down

=INDEX(Sheet1!$C$3:$H$6,MATCH(Sheet2!$A2&Sheet2!$B2,Sheet1!$A$3:$A$6&Sheet1!$B$3:$B$6,0),MATCH(Sheet2!C$1&"invoiced",Sheet1!$C$2:$H$2&Sheet1!$C$1:$H$1,0))-INDEX(Sheet1!$C$3:$H$6,MATCH(Sheet2!$A2&Sheet2!$B2,Sheet1!$A$3:$A$6&Sheet1!$B$3:$B$6,0),MATCH(Sheet2!C$1&"accrued",Sheet1!$C$2:$H$2&Sheet1!$C$1:$H$1,0))

 

[dreid1011]

You may also try this non array formula:

=SUMPRODUCT((Sheet2!$A$3:$A$6=$A2)*(Sheet2!$B$3:$B$6=$B2)*(Sheet2!$C$2:$H$2=C$1)*(Sheet2!$C$1:$H$1="INVOICED")*(Sheet2!$C$3:$H$6))-SUMPRODUCT((Sheet2!$A$3:$A$6=$A2)*(Sheet2!$B$3:$B$6=$B2)*(Sheet2!$C$2:$H$2=C$1)*(Sheet2!$C$1:$H$1="ACCRUED")*(Sheet2!$C$3:$H$6))

Change Sheet2 to match your data sheet: Name wasn't clear.. Data Sheet or Data Sheet #1 (ie: Sheet2 change to 'Data Sheet' / 'Data Sheet #1' accordingly)

 

[jeongs1]

It's returning me the date... but in the format of Jan-00.
I double checked and triple checked the formula but still returns the result as Jan-00

 

[dreid1011]

It's returning me the date... but in the format of Jan-00.
I double checked and triple checked the formula but still returns the result as Jan-00

Which formula did you use?

 

[jeongs1]

Which formula did you use?

The non-array formula you showed me. I just tried the array formula and gave me the same result.

 

[Jonmo1]

I have been just manually doing SUMIFS using product and adv type.

Can you post an example of the formula you've been using manually thus far?

 

[dreid1011]

The non-array formula you showed me. I just tried the array formula and gave me the same result.

What is the cell formatted as where you are putting the formula?

 

[jeongs1]

Can you post an example of the formula you've been using manually thus far?

Well, manually, I've been doing SUMIFS using just the product and the advertising type. And going through each month and dragging the formula. But I want to know if I can add the month in there so that I don't have to do it. All I have to do is input the data inside the data sheet, and let the formula do its job in the input sheet.

 

[jeongs1]

All the cells are formatted the same way.

What is the cell formatted as where you are putting the formula?