Sum Week To Date Sales

[Lewzerrrr]

Hi

I have a 26 week daily sales data dump that I need to sum X week week to date

I have my week number (1, 2.. 26) along row 1, split out by Sunday, Monday... Saturday in row 2 with sales data below

Below is required output, how can I go about this?

	1	1	1	1	1	1	1	2	2	2	2	2	2	2	3	3	3	3	3	3	3
Product	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
A	88	70	57	78	29	87	44	78	53	87	91	68	49	97	15	10	48	20	65	7	86
B	31	11	17	79	44	48	36	98	100	3	23	43	4	8	78	49	61	63	10	23	72
C	100	79	55	96	37	56	90	74	58	84	76	70	12	72	65	60	58	59	86	88	36
D	2	98	92	12	81	5	18	50	20	24	11	14	44	33	13	23	36	5	35	55	48
E	64	6	2	54	19	2	30	16	51	45	45	41	10	96	5	22	59	35	76	76	33
Product:	C
Week:	2
Day:	Thursday
Result:	362

<tbody>
</tbody>

Thanks

 

[XOR LX]

Hi,

Assuming your data table, Product, Week and Day are in A1:V8, B9, B10 and B11 respetively:

=SUMPRODUCT(SUMIFS(INDEX(A:XFD,MATCH(B9,A:A,0),0),1:1,B10,2:2,CHOOSE(MATCH(LEFT(B11,2),{"F","M","S","Su","T","Tu","W"}),"<>Sa",{"Su","M"},"*","Su",{"Su","M","T","W"},{"Su","M","Tu"},{"Su","M","Tu","W"})&"*"))

Regards

 

[Lewzerrrr]

Hi,

Assuming your data table, Product, Week and Day are in A1:V8, B9, B10 and B11 respetively:

=SUMPRODUCT(SUMIFS(INDEX(A:XFD,MATCH(B9,A:A,0),0),1:1,B10,2:2,CHOOSE(MATCH(LEFT(B11,2),{"F","M","S","Su","T","Tu","W"}),"<>Sa",{"Su","M"},"*","Su",{"Su","M","T","W"},{"Su","M","Tu"},{"Su","M","Tu","W"})&"*"))

Regards

Thank you, this looks like the devils formula!

I used a helper column for now, inserted before column A. A3 = =SUM(INDEX(C3:W3,MATCH($C$10,$C$1:$W$1,0)):INDEX(C3:W3,MATCH(1,($C$10=$C$1:$W$1)*($C$11=$C$2:$W$2),0))) (Ctrl Shift Enter) and C12 = =INDEX(A3:A7,MATCH(C9,B3:B7,0))

Thanks

 

[sheetspread]

Try:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		1	1	1	1	1	1	1	2	2	2	2	2	2	2	3	3	3	3	3	3	3
2	Product	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3	A	88	70	57	78	29	87	44	78	53	87	91	68	49	97	15	10	48	20	65	7	86
4	B	31	11	17	79	44	48	36	98	100	3	23	43	4	8	78	49	61	63	10	23	72
5	C	100	79	55	96	37	56	90	74	58	84	76	70	12	72	65	60	58	59	86	88	36
6	D	2	98	92	12	81	5	18	50	20	24	11	14	44	33	13	23	36	5	35	55	48
7	E	64	6	2	54	19	2	30	16	51	45	45	41	10	96	5	22	59	35	76	76	33
8
9	Product:	C
10	Week:	2
11	Day:	Thursday
12	Result:	362
Sheet5

Cell Formulas
Range	Formula
B12	=SUM(OFFSET(INDEX($B$3:$V$7,MATCH(B9,$A$3:$A$7,0),MATCH(B10,$B$1:$V$1,0)),,,,MATCH(B11,$B$2:$H$2,0)))

or:

=SUMPRODUCT((($A$3:$A$7=B9)*$B$1:$V$1=B10)*(MOD(COLUMN(B1:V1)-2,7)+1<=MATCH(B11,B2:H2,0))*$B$3:$V$7)


Weekday, weeknum, etc. works if the headers are actual dates.

Of course the solution is even easier when your data is in normal, unpivoted format.