EXCEL FORMULA HOW TO BUILD ( HELP ! ) SUMMING

[Dmrs92]

Hello,

So I have summed values based on week in this configuration 202301, 202302, 202303, 202304...... it is in a row. Below I have summed values based on this headers from pretty big data sheet I used sum product.

My question is, I would like to have a formula which will work like that : I have list with concrete value ( separate sheet ) for example I would like to add 3000 to a specific range. So I put my data in that list with putting start as 202301 and end 202306, and then excel add this value to 202301,202302,202303,202304,202305,202306 and stops. Is it possible without macro ??


Thank you in advance,

 

[Dmrs92]

It will be added to every sumproduct formula. Thanks.

 

[Sufiyan97]

Can you post your data with some expected results using xl2bb?

 

[awoohaw]

Not quite sure what you are asking for. A sheet with data and expected results would be very nice.
But if you just want to add 3000 to data in another location, here is a example.

You can also you the copy/paste spectial (ADD) by typing 3000 in a cell, copy that, then paste special ADD to the entire range.

The example below assumes you have a version of excel that can do arithmetic wiht arrays. Please update your profile so the version you use is displayed as not all solutions the forum may give you will work on all versions of excel.

mr excel questions 15.xlsm
	A	B	C	D	E	F
8
9	202301	202302	202303	202304	202305	202306
10	9039	6463	8602	9683	9315	6486
11	6279	9476	6808	8004	3565	8004
12	8809	7130	7498	6486	6923	8740
13	6187	5796	6072	5888	4646	7268
14	9246	6233	8050	9223	6808	4899
15	5589	3496	6187	6624	8418	7406
16	8165	6808	7590	7245	7613	8648
17	6624	6923	7728	9246	9200	3956
18	7268	3703	4025	8326	6164	3657
19	5037	9131	3634	6716	6233	5152
20
21
22	202301	202302	202303	202304	202305	202306
23	12039	9463	11602	12683	12315	9486
24	9279	12476	9808	11004	6565	11004
25	11809	10130	10498	9486	9923	11740
26	9187	8796	9072	8888	7646	10268
27	12246	9233	11050	12223	9808	7899
28	8589	6496	9187	9624	11418	10406
29	11165	9808	10590	10245	10613	11648
30	9624	9923	10728	12246	12200	6956
31	10268	6703	7025	11326	9164	6657
32	8037	12131	6634	9716	9233	8152
Sheet20

Cell Formulas
Range	Formula
A23:F32	A23	=A10:F19+3000
Dynamic array formulas.

 

[Dmrs92]

It is more clear ?

 

[awoohaw]

you posted an image, not data that the forum can easily work with.

but do you want the value increasing by 3000 each month?

is this what you want:
(if it isn't please give expectations, and also update your profile so we know what version you are using please.)

mr excel questions 15.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
8
9	Period	202301	202302	202303	202304	202305	202306	202307	202308	202309	202310			Start	Stop	Value
10	Product Sum	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000		Product	202302	202306	3000
11		3000	6000	6000	6000	6000	6000	3000	3000	3000	3000
12
13
14
Sheet20

Cell Formulas
Range	Formula
B11:K11	B11	=IF(AND(B$9>=$N$10,B$9<=$O$10),$P$10,0)+B10

 

[Dmrs92]

Yes, but add volume from P10, add leave base volume from my formula. so I guess that I need to =IF(AND(B$9>=$N$10,B$9<=$O$10),MYFORMULA,0)+P10

Sometimes you should just try, not have easy solutions, I know AND formula :D - so it was quite obvious, many thanks.

 

[Dmrs92]

Last one question how to make it for a list :

And for every period add value for few products ?

 

[awoohaw]

Yes, but add volume from P10, add leave base volume from my formula. so I guess that I need to =IF(AND(B$9>=$N$10,B$9<=$O$10),MYFORMULA,0)+P10

Sometimes you should just try, not have easy solutions, I know AND formula :D - so it was quite obvious, many thanks.

I have no idea wha t"MYFORMULA" is.
Also, as I asked...if the solution doesn't solve your question, please post what your expected results are?

 

[awoohaw]

Last one question how to make it for a list :

View attachment 87989


And for every period add value for few products ?

again... please post what you think it will look like? What do you mean by a list?