Lookup/Index match help

[ardykav]

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

 

[Eric W]

SUMIFS comes to mind. For example:

	A	B	C	D	E
1	Week Beginning	Chains	Year	Month	Sum
2	4-Jan	7	2016	2	115
3	11-Jan	7
4	18-Jan	7
5	25-Jan	7
6	1-Feb	8
7	8-Feb	9
8	15-Feb
9	22-Feb	98
10	29-Feb
11	7-Mar
12	14-Mar
13	21-Mar
14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Summary​

Worksheet Formulas Cell Formula E2 =SUMIFS(Summary!$B:$B,Summary!$A:$A,">="&DATE(C2,D2,1),Summary!$A:$A,"<"&DATE(C2,D2+1,1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.

 

[FDibbins]

Try this...

	A​	B​	C​	D​	E​
1​	Week Beginning	Chains
2​	1/4/2016​	7​		2/1/2016​	115​
3​	1/11/2016​	7​
4​	1/18/2016​	7​
5​	1/25/2016​	7​
6​	2/1/2016​	8​
7​	2/8/2016​	9​
8​	2/15/2016​
9​	2/22/2016​	98​
10​	2/29/2016​
11​	3/7/2016​
12​	3/14/2016​
13​	3/21/2016​

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

 

[ardykav]

That is perfect thanks a lot for your help

Try this...

	A​	B​	C​	D​	E​
1​	Week Beginning	Chains
2​	1/4/2016​	7​		2/1/2016​	115​
3​	1/11/2016​	7​
4​	1/18/2016​	7​
5​	1/25/2016​	7​
6​	2/1/2016​	8​
7​	2/8/2016​	9​
8​	2/15/2016​
9​	2/22/2016​	98​
10​	2/29/2016​
11​	3/7/2016​
12​	3/14/2016​
13​	3/21/2016​

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

 

[FDibbins]

Happy to help

(Eric's formula should work as well)