Subtracting from a total and show remaning

mewingpants

New Member
Joined
Apr 1, 2019
Messages
12
Hello,
Ok so what I am trying to do is, I have a total of 500.
And I have different accounts I want the 500 to come out of like for example:

(A1) (B1) (C1)
Dan. 100. ¤
Bob. 50. ¤
Mark. 50. ¤
John. 25. ¤
Alex. 200. ¤
Josh. 300. ¤

TOTAL. REMAINING
500.

And from the total I need subtracted from accounts of my choosing.

I have check boxes by each account so i can choose which accounts the 500 comes out of.
Ok now below this on the same sheet I have the same accounts but what accounts i select on top effects the bottom like a before and after type scenario.
Example,
Say I use Dan (100), john (25), Alex(200) which equals 325, so remaning only 175.
I then choose Josh last to cover the remainder. So the bottom would reflect as so. 300-175=125
And I used all the numbers in Dan,John,alex so they are all depleted showing zeros.

Dan. 0.
Bob. 50.
Mark. 50.
John. 0.
Alex. 0.
Josh. 125.



I've tried a bunch of things I could think of i keep either having numbers go negative so i use MAX,0 and tried to use MAX with IF statements and sumifs I just can not figure out the right combination to do this.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
@mewingpants
Maybe.....

Excel 2010
ABC
1Name£Checkbox
2Dan100TRUE
3Bob50FALSE
4Mark50FALSE
5John25TRUE
6Alex200TRUE
7Josh300TRUE
8
9TOTAL REMAINING
10500
11
12
13
14NameBalance
15Dan0
16Bob50
17Mark50
18John0
19Alex0
20Josh125
Sheet1
Cell Formulas
RangeFormula
B15=IF(A$10-SUMIF(C$2:C2,TRUE,B$2:B2)<0,MIN(B2,ABS(A$10-SUMIF(C$2:C2,TRUE,B$2:B2))),IF(C2,MAX(0,B2-SUM(B$2:B2)),B2))


It looks a bit bulky but I did bolster it in case there are more than one selected names effectively with surplus.
NB it only alculates top to bottom.

Hope that helps.
 
Upvote 0
wow, amazing.... that works perfectly. Thank you so much, I have been messing around for hours trying to figure a way to do this.
 
Upvote 0
Ok just one question though, so say the number for the TOTAL is met being 500, would their be a way to not subtract anymore number even if you select more check boxes than you need?
 
Upvote 0
It does that now if the first selected value >= 500
Do you mean if 500 for any selected value it should be used in preference?
Can you illustrate that scenario?
 
Upvote 0
Not sure if I did something wrong but it works perfect when select dan,john,alex and josh to get the 500 and shows the remaining amount Josh has which is 125.
Ok so the condition is met but say i also choose Bob and Mark, the bottom graph is turn Bob and Mark to zero if though the 500 was already reached like it resets maybe? Would their be a safety formula to prevent IF condition met (the 500) that it will not subtract anymore accounts if the checkbox is checked? Sorry if this is not clear it's just this chart is just a demo the real one has numbers into the hundred millions.
 
Upvote 0
If you are wanting something other than what I have provided then I am afraid that it is not clear to me.
My formula works top to bottom on the account list and allocates only for accounts that are checked, TRUE. Once the Total Remaining ammount has been allocated, it will not allocate frome names below, even if they are checked.
For example...

Excel 2010
ABC
1Name£Checkbox
2Dan100TRUE
3Bob50TRUE
4Mark50TRUE
5John25TRUE
6Alex200TRUE
7Josh300TRUE
8
9TOTAL REMAINING
10125
11
12
13
14NameBalance
15Dan0
16Bob25
17Mark50
18John25
19Alex200
20Josh300
Sheet1
Cell Formulas
RangeFormula
B15=IF(A$10-SUMIF(C$2:C2,TRUE,B$2:B2)<0,MIN(B2,ABS(A$10-SUMIF(C$2:C2,TRUE,B$2:B2))),IF(C2,MAX(0,B2-SUM(B$2:B2)),B2))
 
Upvote 0
Ok awesome that was exactly what I was trying to do. Once the total is met do not subtract from anymore cells even if it is marked true. Thanks for your time I'll look over the forumla's to understand more of how they work, still learning excel.


Again thank you for all your help I really do appreciate it.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top