Lookup/Index match help

A

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
172
Office Version
  1. 365
Platform
  1. Windows
I have a yearly Summary tab (see below) which tracks the amount of chains sold per week. Column A is week beginning and Column B is "Chains", I have several more items but just need to concentrate on one as I will be able to work out the rest once given the first formula

Week Beginning Chains
4/01/2016 7
11/01/2016 7
18/01/2016 7
25/01/2016 7
1/02/2016 8
8/02/2016 9
15/02/2016
22/02/2016 98
29/02/2016
7/03/2016
14/03/2016
21/03/2016


I then have a monthly tab where I want to put in the first day of the month and it will automatically calculate how many chains were sold in this month on the basis of what is in the yearly tab. I have tried index match by date but it only picks up one of the cells on the yearly tab.

Chains
1/02/2016 XXX


All help greatly appreciated,

thanks
 
Sort by date Sort by votes
SUMIFS comes to mind. For example:

ABCDE
Week BeginningChainsYearMonthSum

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4-Jan[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]115[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]11-Jan[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]18-Jan[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]25-Jan[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1-Feb[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]8-Feb[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]15-Feb[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]22-Feb[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]29-Feb[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]7-Mar[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]14-Mar[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]21-Mar[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=SUMIFS(Summary!$B:$B,Summary!$A:$A,">="&DATE(C2,D2,1),Summary!$A:$A,"<"&DATE(C2,D2+1,1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This example has the formula on the same Summary sheet, but since it has the sheet name in it, it can be used on a different sheet.
 
Upvote 0
Try this...
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]Week Beginning[/td][td]Chains[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
2​
[/td][td]
1/4/2016​
[/td][td]
7​
[/td][td][/td][td]
2/1/2016​
[/td][td]
115​
[/td][/tr]

[tr][td]
3​
[/td][td]
1/11/2016​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
4​
[/td][td]
1/18/2016​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
5​
[/td][td]
1/25/2016​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td]
2/1/2016​
[/td][td]
8​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]
2/8/2016​
[/td][td]
9​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td]
2/15/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
2/22/2016​
[/td][td]
98​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
2/29/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]
3/7/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]
3/14/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
13​
[/td][td]
3/21/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
E2=SUMIFS(B:B,A:A,">="&D2,A:A,"<"&EDATE(D2,1))

Note that I am using the USA date format mm/dd/yyyy, but the formula will work with your date format, too
 
Upvote 0
That is perfect thanks a lot for your help

FDibbins said:
Try this...
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]Week Beginning[/td][td]Chains[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
2​
[/td][td]
1/4/2016​
[/td][td]
7​
[/td][td][/td][td]
2/1/2016​
[/td][td]
115​
[/td][/tr]

[tr][td]
3​
[/td][td]
1/11/2016​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
4​
[/td][td]
1/18/2016​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
5​
[/td][td]
1/25/2016​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td]
2/1/2016​
[/td][td]
8​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]
2/8/2016​
[/td][td]
9​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td]
2/15/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
2/22/2016​
[/td][td]
98​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
2/29/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]
3/7/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]
3/14/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
13​
[/td][td]
3/21/2016​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
E2=SUMIFS(B:B,A:A,">="&D2,A:A,"<"&EDATE(D2,1))

Note that I am using the USA date format mm/dd/yyyy, but the formula will work with your date format, too
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
375
Madam_Xavier
M
A
  • Solved
Number of Weeks Between First and Last of a List of Dates
Replies
7
Views
299
hagia_sofia
hagia_sofia
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
93
Skyybot
Skyybot
D
  • Solved
INDEX-MATCH with a vertical lookup
Replies
4
Views
610
Darren_workforce
D
J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
804
Peter_SSs
Peter_SSs

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