Excel Formula Adjustment

[Awolbert]

I need help with an "IF" formula. I received tips from this community to create formulas in the attached spreadsheet but I need one more contingency formula. I am offering quantity discounts in two tiers and the formulas work well but I cannot offer the quantity discounts on custom product. Therefore if a Custom Quote number appears in column D, there will be no discount offered on Column H (so I and J would be blank, and the extension will be straightforward. See example line 10. 

Is there a formula that would disallow a discount in Columns I and J IF there is a custom quote in D? Then could the extension column be adjusted as well to merely create a sum of the quantity and cost each? Thank you so much to anyone who can help. 

[#2]

Try:

I5 =IF(($E5>0)*($E5<=5)*($D5=0),$H5*(1-$I$2),0)
J5 =IF(($E5>5)*($D5=0),$H5*(1-$J$2),0)
K5 =(D5+(D5=0)*MAX(I5,J5))*E5

[#3]

@Awolbert ,,

Check the attached WB and find the modified formula in Column I and J,, read the comment also.

[#4]

@Rajesh_Sinha What about the extension column K? See attached WB green highlights. Can you adjust the sum in column K to capture H, I, J multiplied by E? 

[#5]

@Rajesh_Sinha  Nevermind! I think I got it from a previous reply. Thank you so much. How do I change the text in columns I and J? I would like it to be blank. Dollar sign format. 

[#6]

@JMB17 You are awesome!!!! Thank you.

[#7]

@JMB17 See attached. Extension in column K is not correct. What did I do wrong?

[#8]

@Awolbert 

Some of the formulas are referencing cells in a different row. I went ahead and added a conditional format to say "No Discount" for  custom quotes - if you prefer that instead of just showing zero.

[#9]

@Awolbert 

Check the Column K I've edited the formula also column I where I have replaced TEXT with Zero.  Hope this work for you.

[#10]

@Rajesh_Sinha I need an IF, THEN formula for Column K when Column D is filled. Please see the attached page. And thank you so much for your quick responses. You are helping me immensely. 

[#11]

@Awolbert 

Check this ,, hope will work,,, Read comments & some sample formula filled with Yellow.

[#12]

@Rajesh_Sinha Thank you for the formula to total custom quotes, but can that command be added to the previous formula somehow. I am sorry to be such a problem. How can I get ONE formula to give me a total in Column K for each line? See my revised worksheet and comments. 

[#13]

@Rajesh_Sinha Just one more request! See the worksheet. Can you combine the formulas (yellow and green) to create a total for EACH line? 

[#14]

Oh, I originally thought column D was a price quote. Try this in K5:

=((D5<>"")*H5+(D5="")*MAX(I5,J5))*E5

[#15]

Wahoo!!!!!! It works! That is exactly what I need. Thank you to you, JMB17!

[#16]

Sorry about that, I misunderstood before.

[#17]

I finally figured out that Column D was misconstrued as a price! My mistake. Thanks again for the help of this community.

[#18]

@Awolbert 

Check M5:M11,,, if this works, the you may accept my post & Answer as well Like ☺

[#19]

@Rajesh_Sinha Thank you but I do not need a running total with each line. These are all products specified for a project to I need to keep a total for each line and then a final project total at the end. Thank you for all of your help. I have it figured out now.

[#20]

@Awolbert ,,

Unfortunately every time I solve the issue & post, you give a new twist to the question, under theses circumstances it's been very difficult to realize your exact need !!