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]

regarding this: :

why aren't you adding product 2 values to the total?

ONE MORE TIME :

PRODUCT 1 - VALUE 1000 - START PERIOD 202301 - STOP PERIOD 202305 - HERE I WANT THAT FORMULA WOULD ADD 1000 to base value in period 202301, 202302, 202303,202304, 202305
PRODUCT 2 - VALUE 2000 - START PERIOD 202301 - STOP PERIOD 202304 - HERE I WANT THAT FORMULA WOULD ADD 2000 to base value in period 202301, 202302, 202303,202304
PRODUCT 3 - VALUE 3000 - START PERIOD 202305 - STOP PERIOD 202307 - HERE I WANT THAT FORMULA WOULD ADD 3000 to base value in period 202305, 202306, 202307

The list is used to add new coming projects so you want to keep it separate -knowing what is what ( Sometimes something will be deleted so you don't want to keep it in one line)

So now I have a by base table :

In row 16 and 17 - this is my base values which in my file is calculated from another sheet ( this is ok I don't want to do anything with that)

ROW 20, 21, 22 - I just showed values which are connected to a list in columns O, P, Q, R ( I don't want that table exist, I just showed what values should be add where ). Just interpretation of list (nothing else).

Foe example if you have Product 1 and Product 2 - both have period 202301 in it's range - so you add 1000 for product 1 and 2000 for product 2 to base value which is 1000 for period 202301.

Therefore you have outcome in rows 25 and 26 - base 1000 plus 1000 from OPQR columns ( product 1 ) and also 2000 from ( product 2 ), the outcome in this situation is 4000. ( Example for period 202301)


I think more clear now, thanks.

 

[fjns]

Hi, see the linked file for a possible solution...

The formula used in the table:
=B17+SUM(($P19:$P21<=B16)*($Q19:$Q21>=B16)*($R19:$R21))

Summary.xlsx

 

[awoohaw]

Hi, see the linked file for a possible solution...

The formula used in the table:
=B17+SUM(($P19:$P21<=B16)*($Q19:$Q21>=B16)*($R19:$R21))

Summary.xlsx

View attachment 88116

@fjns , I haven't looked at your file, but good luck in your attempts to solve this. I'm out.

 

[fjns]

@fjns , I haven't looked at your file, but good luck in your attempts to solve this. I'm out.

@awoohaw
Thank you for your honourable opinion.
You are not out, your advantage is devastating (79:12).

 

[awoohaw]

@awoohaw
Thank you for your honourable opinion.
You are not out, your advantage is devastating (79:12).

@fjns , thanks

 

[Dmrs92]

Hello,

It works only for first row in formula in my wxcel it is typed like this : SUM(($AJ$4:$AJ$20<=B3)*($AK$4:$AK$20>=B3)*($AL$4:$AL$20)), and in your excel it works for every row. Any idea why ?

 

[fjns]

Sorry, I don't know wxcel.