How to sum in each cell until a condition is met

Posted on

QUESTION :

Employees can buy products from our inventory and we can deduct the cost monthly in their checks. We help them by setting a small monthly deduction of either: $25,$50,$75 or $100.

I need help with a formula that I can drag in each cell and tell me when to stop deducting the agreed amount.

In the link below you could see how I’ll be needing a formula that I can drag and can give me the desired result in Dec, Apr, or May.
Thank you in advance!

excel sample

ANSWER :

use some nested ifs:

=IF(COLUMN()=3,IF($B2<=A2,$B2,$A2),IF(SUM(B2:$C2)=$A2,"",IF(SUM(B2:$C2)+$B2>$A2,$A2-SUM(B2:$C2),$B2)))

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *