Split amounts into various cells based on value of a different cell

seriousdamage

Board Regular
Joined
Aug 14, 2005
Messages
58
Hello All,

I am trying to organize data from a small balance sheet into various virtual accounts.
I have 4 accounts where I like to split the income, each account has a purpose.

Please see image attached.

The idea is that I have a sheet where I record all transactions for money coming in and out (Column B to E)
Each transaction will only have 2 categories, "IN" and "OUT"
The plan is if I add a transaction with the category "IN" than I like to split the amount for that transaction into the for accounts based on the 4 different % that you can see in columns H,
the more I add this category and the amounts should keep summing up

If I add a category "OUT" than the amount in column E should be subtracted from Account 4 in cell J7

Lastly, the idea is that C2 should always be equal to J2, if it's not then I know I made a mistake somewhere.

Could someone help me?
Thanks so much
Regards
Nic
 

Attachments

  • Account split.PNG
    Account split.PNG
    22 KB · Views: 3

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
will that works for you?

Book1
ABCDEFGHIJK
1
2Balance3000Balance3000
3Amount%
420000.15Account1450
5-2000.45Account21350
615000.1Account3300
7-3000.3Account4900
8
Sheet6
Cell Formulas
RangeFormula
C2C2=SUM(E:E)
J2J2=SUM(J4:J7)
J4:J7J4=SUM(E:E)*H4
 

seriousdamage

Board Regular
Joined
Aug 14, 2005
Messages
58
Hi Alan, thanks for the above,
your solution made me realize that I did not think this trough 100%.
The formulas you have works well only when I bring money in.. the amount gets split amongst the four accounts.
However, I need a way to take money out of each account separately,
Account4 are "Costs" so If I add a negative amount (payment) in column E that is a cost, it should only be detracted from Account 4,
Account3 are "Taxes" so If I add a negative amount (payment) in column E that is related to me paying taxes, it should only be detracted from Account 3

That is why I wanted to use a "category field". Does it make sense?

Thanks a lot
Nic
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Alan, thanks for the above,
your solution made me realize that I did not think this trough 100%.
The formulas you have works well only when I bring money in.. the amount gets split amongst the four accounts.
However, I need a way to take money out of each account separately,
Account4 are "Costs" so If I add a negative amount (payment) in column E that is a cost, it should only be detracted from Account 4,
Account3 are "Taxes" so If I add a negative amount (payment) in column E that is related to me paying taxes, it should only be detracted from Account 3

That is why I wanted to use a "category field". Does it make sense?

Thanks a lot
Nic

so, for example E5 is -200 (i.e. OUT), which account (3 or 4) to detracted from?
 

seriousdamage

Board Regular
Joined
Aug 14, 2005
Messages
58

ADVERTISEMENT

so, for example E5 is -200 (i.e. OUT), which account (3 or 4) to detracted from?

It would depend on the category, If I would use "Costs" then it needs to be detracted from account 4,
if I use category "Taxes" it needs to go out of account 3
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this, adjust ranges to suit

Book1
ABCDEFGHIJ
1
2Balance3000Balance3000
3Amount%
4In20000.15Account1525
5Costs-2000.45Account21575
6In15000.1Account350
7Taxes-3000.3Account4850
8
Sheet6
Cell Formulas
RangeFormula
C2C2=SUM(E:E)
J2J2=SUM(J4:J7)
J4:J5J4{=SUM(IF($C$4:$C$30="In",$E$4:$E$30)*$H4)}
J6J6{=SUM(IF($C$4:$C$30="In",$E$4:$E$30)*$H6)+SUM(IF($C$4:$C$30="Taxes",$E$4:$E$30))}
J7J7{=SUM(IF($C$4:$C$30="In",$E$4:$E$30)*$H7)+SUM(IF($C$4:$C$30="Costs",$E$4:$E$30))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


another option

Book1
ABCDEFGHIJK
1
2Balance3000Balance3000
3Amount%
4In20000.15Account1525
5Costs-2000.45Account21575
6In15000.1Account350
7Taxes-3000.3Account4850
8
9
Sheet6
Cell Formulas
RangeFormula
C2C2=SUM(E:E)
J2J2=SUM(J4:J7)
J4:J7J4=H4*SUMPRODUCT($E$4:$E$7,--($C$4:$C$7="In"))+IF(I4="Account3",SUMPRODUCT($E$4:$E$7,--($C$4:$C$7="Taxes")),IF(I4="Account4",SUMPRODUCT($E$4:$E$7,--($C$4:$C$7="Costs")),0))
 

seriousdamage

Board Regular
Joined
Aug 14, 2005
Messages
58
The first option is the solution that worked, I added a couple of categories and it does precisely what I needed.
Thanks a lot for sticking with it

Regards
Nic
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Glad that we got it sorted
 

Watch MrExcel Video

Forum statistics

Threads
1,127,678
Messages
5,626,227
Members
416,169
Latest member
DROP_DATABASE_MrExel

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
Top