Sales for the 6 first months?

[Erza]

Hi

I'm trying to calculate periodically sales for new products, which have been in the market for max 6 monts. After that 6 months the sales of the product is not to be calculated. I have a huge amount of products, where this information should be calculated, so manually calculating is not an option. The products are in rows, and periods are in columns. As the data concerns several years data there is a problem, that some products have in some months zero sales, and in the next month again some sales. This messes up always my calculations. How to truly take only the first 6 months, and leave all the rest uncalculated?

How should I do this? Sorry, I tried, but wasn't able to put an example here...

Many thanks for your help,
Erza

 

[Hazen]

Welcome to the brd; you mention that dates are in columns so I'll assume column headers are exact dates in the first row. Try this in any cell below your data (or modify to be on a different sheet)

=SUMIF($1:$1,">"&TODAY()-182.5,2:2)

then this can be copied down for each product
note that 182.5=365/2 or 6 mths

 

[GorD]

Hi

How to truly take only the first 6 months, and leave all the rest uncalculated?

Erza[/quot

We really need some sample data.

I think Hazens formula would provide sales for the last 6 months, which if I understand correctly isn't what you need.

 

[Erza]

Thanks Hazen and GorD for your answers.

GorD, you got it right, just the first 6 months data even if sales continues after that. In the columns there are (row 1) P105, P205, P305 and so on. On the rows (column 1) there are product A, B, C, D, and so on. Sales volumes are there calculated periodically for instance for A:
on row2: 10, 10, 10, 10, 10, 10, 10,.... From this one should take only the first 6 months values to the addition calculated for the period down below.

I would have put better data here if I would have known how...

Br, Erza

 

[Hazen]

wow, I sure missed the aim right in your title - GorD is correct, my entry gives last 6 mths. Try this:

=SUMIF($1:$1,"<"&MIN($1:$1)+182.5,2:2)

still assuming dates entered in your headers although that may need clarification

still using $ to lock link on header so formula can be copied down

 

[GorD]

Heres my effort

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	First 6M		period 1	period 2	period 3	period 4	period 5	period 6	period 7	period 8	period 9	period 10
2	75	Product 1	10	11	12	13	14	15	16	17	18	19
3	75	Product 2			10	11	12	13	14	15	16	17
4	60	Product 3					10	10	10	10	10	10
5	165	Product 4				25	26	27	28	29	30	31
6	75	Product 5		10	11	12	13	14	15	16	17	18
Sheet1

See what you think

 

[Erza]

GorD, thanks for your effort!
Now the calculation is done for each product separately. Good information though, but how could that be done so that the data is calculated for every period for those products which are new in that period? For p1: 10, P2: 21, ...., P9: 40, P10: 10?

Thanks again,
Erza

 

[GorD]

So for my example what would your desired result be ?
I don't fully understand your last post - please explain further.

 

[Erza]

GorD,
The results for your example would then be: For p1: 10 (in C7), P2: 21 (in D7), ...., P9: 40 (in K9) , P10: 10 (in L9). Calculate the values fo every column (period) of the new - yellow products. Sorry for my previous unclear message. Hope this clarifies a bit.

Erza

 

[GorD]

Okay, I understand the question, just not sure how to do it at present.

I'll need to have a think about that

Maybe someone else can help out as I won't be able to look at it till after work