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

[seriousdamage]

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

 

[AlanY]

will that works for you?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2		Balance	3000						Balance	3000
3					Amount			%
4					2000			0.15	Account1	450
5					-200			0.45	Account2	1350
6					1500			0.1	Account3	300
7					-300			0.3	Account4	900
8
Sheet6

Cell Formulas
Range	Formula
C2	C2	=SUM(E:E)
J2	J2	=SUM(J4:J7)
J4:J7	J4	=SUM(E:E)*H4

 

[seriousdamage]

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]

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]

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]

try this, adjust ranges to suit

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		Balance	3000						Balance	3000
3					Amount			%
4			In		2000			0.15	Account1	525
5			Costs		-200			0.45	Account2	1575
6			In		1500			0.1	Account3	50
7			Taxes		-300			0.3	Account4	850
8
Sheet6

Cell Formulas
Range	Formula
C2	C2	=SUM(E:E)
J2	J2	=SUM(J4:J7)
J4:J5	J4	{=SUM(IF($C$4:$C$30="In",$E$4:$E$30)*$H4)}
J6	J6	{=SUM(IF($C$4:$C$30="In",$E$4:$E$30)*$H6)+SUM(IF($C$4:$C$30="Taxes",$E$4:$E$30))}
J7	J7	{=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
	A	B	C	D	E	F	G	H	I	J	K
1
2		Balance	3000						Balance	3000
3					Amount			%
4			In		2000			0.15	Account1	525
5			Costs		-200			0.45	Account2	1575
6			In		1500			0.1	Account3	50
7			Taxes		-300			0.3	Account4	850
8
9
Sheet6

Cell Formulas
Range	Formula
C2	C2	=SUM(E:E)
J2	J2	=SUM(J4:J7)
J4:J7	J4	=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]

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]

Glad that we got it sorted