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

#### seriousdamage

##### Board Regular
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.

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
22 KB · Views: 3

### 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
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
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
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

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
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
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
Glad that we got it sorted

Replies
18
Views
573
Replies
1
Views
32
Replies
0
Views
32
Replies
1
Views
152
Replies
3
Views
74

### Forum statistics

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.

### Which adblocker are you using?

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

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