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?


[TABLE="width: 336"]
<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>[TR]
[TD="width: 28, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="width: 15, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="width: 139, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="width: 88, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="width: 89, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "]Actual[/TD]
[TD="width: 89, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "]Actual[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , colspan: 2"]000 Local Curr (UK Pounds)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "]Jan-18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "]Feb-18
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]REVENUE[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, colspan: 2"] MP/Compact[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, colspan: 2"]Compact[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]0 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 523"]
<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>[TR]
[TD="width: 133, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 244, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Jan-18[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Feb-18[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Mar-18[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Apr-18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Actual[/TD]
[TD="bgcolor: transparent"]Actual[/TD]
[TD="bgcolor: transparent"]Actual[/TD]
[TD="bgcolor: transparent"]Actual[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SUSPENSE [/TD]
[TD="bgcolor: transparent, colspan: 13"] [/TD]
[TD="bgcolor: transparent, colspan: 13"] [/TD]
[TD="bgcolor: transparent, colspan: 13"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]
[/TD]
[TD="bgcolor: transparent"]General Suspense
Account [/TD]
[TD="bgcolor: transparent, align: right"]-0.02[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]-0.01[/TD]
[TD="bgcolor: transparent, align: right"]0.01[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Group Total[/TD]
[TD="bgcolor: transparent, align: right"]-0.02[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]-0.01[/TD]
[TD="bgcolor: transparent, align: right"]0.01[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SALES [/TD]
[TD="bgcolor: transparent, colspan: 13"] [/TD]
[TD="bgcolor: transparent, colspan: 13"] [/TD]
[TD="bgcolor: transparent, colspan: 13"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]
[/TD]
[TD="bgcolor: transparent"]Sales Suspense [/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] MP/Compact[/TD]
[TD="bgcolor: transparent, align: right"]
[/TD]
[TD="bgcolor: transparent"]Sales MP[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]2200[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] MP/Compact[/TD]
[TD="bgcolor: transparent, align: right"]
[/TD]
[TD="bgcolor: transparent"]Sales Other[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Compact[/TD]
[TD="bgcolor: transparent, align: right"]
[/TD]
[TD="bgcolor: transparent"]Sales C[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]14865.76[/TD]
[TD="bgcolor: transparent, align: right"]-734.64[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Compact[/TD]
[TD="bgcolor: transparent, align: right"]
[/TD]
[TD="bgcolor: transparent"]Sales Compact [/TD]
[TD="bgcolor: transparent, align: right"]120991[/TD]
[TD="bgcolor: transparent, align: right"]70831.83[/TD]
[TD="bgcolor: transparent, align: right"]105026[/TD]
[TD="bgcolor: transparent, align: right"]4125[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.