SOLVED

New Contributor

# IF SUM > 40, subtract the difference of total - 40 from specific cell

Regular: 34

Prevailing: 2.50

Shop: 6.00

----------

Total: 42.50

That's example of data. Now, since the total is over 40, subtract 2.50 from SHOP and give new balance.

Anyone have ideas on this? Thank you in advance,

Drezna.

6 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

# Re: IF SUM > 40, subtract the difference of total - 40 from specific cell

Ok, so I did this formula ... and got the results I want. However, I have a secondary question. If there is not enough SHOP to delete to get to 40, can I make it automatically shift to take from REGULAR (after emptying SHOP?)

=IF(SUM(F20:F24)>40,F23-SUM(F25-40),"")

# Re: IF SUM > 40, subtract the difference of total - 40 from specific cell

``=IF(SUM(F20:F24)>40,IF(F25-40<F23,F23-SUM(F25-40),F20-(F25-40-F23)),"total <= 40")``

Is this what you want to do? I entered above formula in cell C23 and copied it across range C23:E23.

# Re: IF SUM > 40, subtract the difference of total - 40 from specific cell

@Quadruple_Pawn Thank you for your reply. I'm sharing my file to show you how far I've gotten on resolving this.

https://1drv.ms/x/s!An-umg36SSwYk7M25UCn0v0RREuhrg?e=KkiXFJ

Thank you

Drezna.

# Re: IF SUM > 40, subtract the difference of total - 40 from specific cell

You can try the attached file. This could be what you want to do.

# Re: IF SUM > 40, subtract the difference of total - 40 from specific cell

``````=SUM(H5:H10) - (G29>40)*(G28>H16)*(G28-H16)