SUMIF (VLOOKUP?) Based on days into weeks?

[agerrard]

Hi All,

I have Excel 2007.

I have a data table that has sales by days (for lots and lots of products, but i've just used a very small sample here).

But i need to sum the daily sales data into weeks. Just not sure how i tell excel how to do that.

Starting in the Table tab and in cell D5 I need to find the product 1012BP10 in the Data tab and then i need to SUM all the sales for that product for the days that make up the week in cell D4 (in the Table tab) and then i need to do the same for the next week and so on...

Excel 2007
	C	D	E	F	G
3	1012BP10
4	Week Commencing	27/04/2015	4/05/2015	11/05/2015	18/05/2015
5	Quantity
Table

Cell Formulas
Range	Formula
E4	=D4+7
F4	=E4+7
G4	=F4+7

Excel 2007
	A	B	C
1	StockCode	InvoiceDay	QtyInPCS
2	1012BP10	05-May-15	120
3	1012BP10	01-May-15	120
4	1012BP10	02-May-15	120
5	1012BP10	04-May-15	120
6	1012BP10	06-May-15	120
7	DH147	15-May-15	120
8	DH147	11-May-15	120
9	1012BP10	22-May-15	120
10	1012BP10	01-May-15	120
11	DH147	21-May-15	240
12	1012BP10	04-May-15	240
13	1012BP10	04-May-15	240
14	CDT4PK	05-May-15	120
15	1012BP10	04-May-15	120
16	CDT4PK	26-May-15	120
17	CDT4PK	04-May-15	120
18	1012BP10	21-May-15	120
19	1012BP10	26-May-15	240
20	CDT4PK	04-May-15	120
21	1012BP10	15-May-15	120
22	CDT4PK	01-May-15	240
23	1012BP10	22-May-15	120
Data

Hopefully this makes sense?

Oh and i need to VLOOKUP in the Data tab as i need to sum multiple products (i just used a small sample here).

Cheers!

 

[cdchapman]

Based on your example data, try the following formula in cell D5 of your table tab, and copy across to the other weeks:

=SUMIFS(Data!$C$2:$C$23,Data!$A$2:$A$23,$C$1,Data!$B$2:$B$23,">="&D$4,Data!$B$2:$B$23,"<="&D$4+6)

Adjust the cell ranges in the formula to suit your data sheet as necessary.


Chris

 

[Adjunctor]

You may try this:

on D5:

=SUMPRODUCT(--(Data[QtyInPCS])*(Data[StockCode]=$C$3)*(Data[InvoiceDay]>=D4)*(Data[InvoiceDay]<=(D4+6)))

For this to work you need to format your data as a table, the advantage of this is that you will never need to worry about adjusting ranges, no matter how much data you add to or remove from your DATA tab. To avoid unwanted changes on format first declare a new table style without declaring any format.

If you don't like to use tables just use a substitute each table call with the corresponding range (i.e Data[QtyInPCS] will become DATA!$A2:$A$? (? means the last row number) but remember, you will have to adjust your range every time you add or delete data from your DATA tab.

Hope this helps.
Saludos Amigo.

 

[agerrard]

Based on your example data, try the following formula in cell D5 of your table tab, and copy across to the other weeks:

=SUMIFS(Data!$C$2:$C$23,Data!$A$2:$A$23,$C$1,Data!$B$2:$B$23,">="&D$4,Data!$B$2:$B$23,"<="&D$4+6)

Adjust the cell ranges in the formula to suit your data sheet as necessary.


Chris

Thanks for you reply cdchapman. I copied the formula but what i noticed was that there is no provision to in the formula to search by the product code (eg. the value in cell C3 in the Table tab).

What im hoping to do is (starting in cell D5 in the Table tab):

1. Look at the value in C3
2. Then go find the sales for that product (column A) in the Data tab (as in SUM the sales) for the DAYS (e.g. in column B) that make up the WEEK in cell D4 etc...

Would it be a VLOOKUP and SUMIF combination?

Thanks

 

[agerrard]

Hi Chris,

Thanks again for you help. I didnt read the formula correctly the first time, but now its working great! Have a good weekend!

 

[cdchapman]

Glad it works - happy to help you out...

Chris