Index Match Sumifs

L

LaBoop

New Member
Joined
Dec 3, 2015
Messages
7
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]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Hi,

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


Book1
ABCDEFG
1ActualActualActualActual
2000 Local Curr (UK Pounds)18-Jan18-Feb18-Mar18-Apr
3
4REVENUE
5MP/Compact0220000
6Compact12099170831.83119891.83390.36
7
8
918-Jan18-Feb18-Mar18-Apr
10ActualActualActualActual
11SUSPENSE
12General Suspense-0.020-0.010.01
13Account
14Group Total-0.020-0.010.01
15SALES
16Sales Suspense0000
17MP/CompactSales MP0220000
18MP/CompactSales Other0000
19CompactSales C0014865.76-734.64
20CompactSales Compact12099170831.831050264125
Sheet111
Cell Formulas
RangeFormula
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Maintaining Historical Data
Replies
1
Views
592
dragonmouse
D
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
S
  • Question Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
983
AlanY
A
N
  • Question Question
How to Create Multiple Reports from One Sheet
Replies
1
Views
2K
Logit
Logit
B
  • Question Question
Matching Number To Number In text
2 3
Replies
23
Views
2K
Twollaston
T

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top