Index Match Sumifs

[LaBoop]

Hi,

I apologise if this has come up before. I have tried solutions in other threads but they have not worked for me.

I need to fill in the 1st table below with data from the 2nd table. I have tried to use a formula to pick up all MP/Compact or Compact sales for the month in that column but it will only pick up the one value instead of summing multiple values. I have tried index match and using sumifs....can anyone help?

				Actual	Actual
	000 Local Curr (UK Pounds)		Jan-18	Feb-18
REVENUE
	MP/Compact		0
	Compact		0

<colgroup><col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="15" style="width: 11pt; mso-width-source: userset; mso-width-alt: 548;"> <col width="139" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5083;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;" span="2"> <tbody> </tbody>

			Jan-18	Feb-18	Mar-18	Apr-18
			Actual	Actual	Actual	Actual
		SUSPENSE
		General Suspense Account	-0.02	0	-0.01	0.01
		Group Total	-0.02	0	-0.01	0.01
		SALES
		Sales Suspense	0	0	0	0
MP/Compact		Sales MP	0	2200	0	0
MP/Compact		Sales Other	0	0	0	0
Compact		Sales C	0	0	14865.76	-734.64
Compact		Sales Compact	120991	70831.83	105026	4125

<colgroup><col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4864;"> <col width="64" style="width: 48pt;"> <col width="244" style="width: 183pt; mso-width-source: userset; mso-width-alt: 8923;"> <col width="64" style="width: 48pt;" span="4"> <tbody> </tbody>

 

[jtakw]

Hi,

If those are Actual Date Values (in my sample, D2:G2 and D9:G9):

Book1
	A	B	C	D	E	F	G
1				Actual	Actual	Actual	Actual
2		000 Local Curr (UK Pounds)		18-Jan	18-Feb	18-Mar	18-Apr
3
4	REVENUE
5		MP/Compact		0	2200	0	0
6		Compact		120991	70831.83	119891.8	3390.36
7
8
9				18-Jan	18-Feb	18-Mar	18-Apr
10				Actual	Actual	Actual	Actual
11			SUSPENSE
12			General Suspense	-0.02	0	-0.01	0.01
13			Account
14			Group Total	-0.02	0	-0.01	0.01
15			SALES
16			Sales Suspense	0	0	0	0
17		MP/Compact	Sales MP	0	2200	0	0
18		MP/Compact	Sales Other	0	0	0	0
19		Compact	Sales C	0	0	14865.76	-734.64
20		Compact	Sales Compact	120991	70831.83	105026	4125
Sheet111

Cell Formulas
Range	Formula
D5	=SUMPRODUCT(($B$11:$B$20=$B5)*(MONTH($D$9:$G$9)=MONTH(D$2))*$D$11:$G$20)

D5 formula copied down and across as needed.

If your data will extend to multiple Years, then we'll also need to add a check for the Year, now we're just checking the Month.