Sales for the first 6 weeks

[realsongs]

Good evening everyone,

I am struggling to come up with a formula to calculate product sales for the first 6 weeks it came into stock. I am working from a Cube pivot but I have attached a mock table for simplicity.

The table attached shows different product sales per week. However some products didn't arrive into the business until week 7, some arrived in week 30. Is there a formula to calculate the sales for the first 6 weeks of a product arriving?

for example for product 2, I want the formula to calculate sales from week 7 to 12, for product 11 it would be from weeks 30 to 35.

Thanks in advance!

 

[Coyotex3]

Assuming you meant to say Product 10 should be calculated from Weeks 30 to 35 as opposed to Product 11?

If so maybe this formula would work:

=IFERROR(SUM(TAKE(FILTER(B2:AO2,B2:AO2<>""),,6)),0)

PERSONAL.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	Total Sales
2	Product 1	93	86	42	35	87	42	58	60	26													385
3	Product 2					53	68	31	47	25	92	19	74	84									316
4	Product 3					43	2	17	8	87	18	82	32	58									175
5	Product 4													11	90	84	64	47	6	42	44	82	302
6	Product 5											54	100	89	80	85	11	49	57	97			419
7	Product 6											73	47	49	73	32	21	90	22	82			295
8	Product 7									39	87	57	84	30	24	35	29	15					321
9	Product 8								12	5	66	1	16	2	20	60	98						102
10	Product 9																						0
11	Product 10						55	32	47	86	27	49	86	53	5								296
12	Product 11						87	5	1	11	89	83	31	10	62								276
Sheet5

Cell Formulas
Range	Formula
W2:W12	W2	=IFERROR(SUM(TAKE(FILTER(B2:V2,B2:V2<>""),,6)),0)

 

[realsongs]

The worked perfectly! thank you for your help.