Forumla help - sumif maybe?

[holly1212]

Hi all,

Am in need of a bit of help with a formula. The formula needs look at the depot cover and calculate the forward forecast. So if depot cover is:

less than or equal to 1 - then it needs to add weeks 34,35,36 and 37.
greater than 1 but, less than or equal to 2 - then add weeks 35, 36, 37
greater than 2 but, less than or equal to 3 - then add weeks 36 and 37
greater than 3 but, lets than or equal to 4 - then = week 37
greater than 4 = 0

Attachment 419148

Hope that makes sense

TIA

[NeedForExcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[holly1212]

Done - example attached. Sums the greens cells

[NeedForExcel]

Try this

=CHOOSE(IF(B2<=1,1,IF(AND(B2>1,B2<=2),2,IF(AND(B2>2,B2<=3),3,IF(AND(B2>4,B2<=4),4,5)))),SUM(I2:L2),SUM(J2:L2),SUM(K2:L2),L2,0)

[JohnTopley]

Try this ...in F2 and copy down

=SUM(OFFSET($A$1,ROW()-1,COUNTA($A$1:$zz$1)-4+INT(($B2+0.5)),1,4))

On the assumption that Week 38 data will follow so you want the last 4, (3, 2,1) weeks i.e 35, 36.37, 38

[JohnTopley]

The "Depot Cover" are misleading as they are not integer values (only displayed as such) so in row 15 the "Depot Cover" is 3.89.... so according to your criteria we should have a value of 55 where as you show 0 in your example.

My formula tried to allow for this but not sure it is robust in this regard.

The other formula could be simplified to

=CHOOSE(IF(B8<=1,1,IF(B8<=2,2,IF(B8<=3,3,IF(B8<=4,4,5)))),SUM(I8:L8),SUM(J8:L8),SUM(K8:L8),L8,0)