Help with cell formula - Demand planning

Hi all
I have the attached excel sheet, and Im trying to work out the sum for cell F11
Cells D2 to D7 contain the number of each device I have in stock, in cells F2 to F7 I have the demand requirement for each of the line items
In cells D11 to D16 I have the number of widgets that need to go into each item
In cell E11 I have a manually enter number that I have in stock
In Cell F11 I want it to show a number that I would need to produce

In the attached example the calculation I have is
=SUM(D2-F2)+(D3-F3)+(D4-F4)+(D5-F5)+(D6-F6)+(D7-F7)
Giving an answer of 150

But I need to have an answer of 0 as I already have enough devices in stock and I also have the parts in stock to make the demand

This has been frustrating me for a few days so any help you can give would be greatly appreciated

Cheers

you could just add an IF

If( the quantity required =< quantity in stock , 0, formula , depends on what you want to show )

IF( (D2-F2)+(D3-F3)+(D4-F4)+(D5-F5)+(D6-F6)+(D7-F7) <= E11, 0, "what to put if not enough in stock")

you also dont need the sum

=IF((D2-F2)+(D3-F3)+(D4-F4)+(D5-F5)+(D6-F6)+(D7-F7)<=E11,0, "NOT enough in stock")

=MAX(0,SUM(D2:D7)-SUM(F2:F7)-E11)

Try this formula

i would suggest to comply with the rules - you modify your title

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

you dont have any values in row 9

how do you get to 540

you have 240 in E11
the formula
(D2-F2)+(D3-F3)+(D4-F4)+(D5-F5)+(D6-F6)+(D7-F7)
makes 150
so what do you need to do , when there is not enough quantity

=MAX(0,SUM(D2:D7)-SUM(F2:F7)-E11)
Is an alternative formula for
=IF((D2-F2)+(D3-F3)+(D4-F4)+(D5-F5)+(D6-F6)+(D7-F7)<=E11,0, "NOT enough in stock")
And not the answer for question #5

1st I would suggest that you remove all of thise merged cells/columns, merging causes more problems than anything else with formulas

2nd, you can shorten that formula to this...
=SUMPRODUCT((D2:D7)-(F2:F7))

And then, if you wAnt to show 0 if the qty on hand is greater, then something like this...

=IF(SUMPRODUCT((D2:D7)-(F2:F7))-E11<0,0,SUMPRODUCT((D2:D7)-(F2:F7))-E11)

Squash, please do not keep posting duplicate threads. I have merged these 2, and closed the other 1. Please continue on this thread