I have this list of accounts, some of them are subaccounts of a higher level account, how can I sum the subaccounts so the main account has the sum of all subaccounts and so on. Example:
Account # Ammount
1120000000 0.00
1120001000 5.00
1150000000 0.00
1150001000 0.00
1150001001 10.00
1150001002 20.00
1150002000 30.00
1160000000 0.00
1160001000 40.00
1160002000 50.00
1170000000 0.00
1170002000 60.00
1190000000 0.00
1190001000 70.00
1190002000 80.00
1190003000 90.00
1210000000 100.00
1220000000 110.00
1230000000 120.00
After running the macro or vba code i should get this:
Account # Ammount
1120000000 5.00
1120001000 5.00
1150000000 60.00
1150001000 30.00
1150001001 10.00
1150001002 20.00
1150002000 30.00
1160000000 90.00
1160001000 40.00
1160002000 50.00
1170000000 60.00
1170002000 60.00
1190000000 240.00
1190001000 70.00
1190002000 80.00
1190003000 90.00
1210000000 100.00
1220000000 110.00
1230000000 120.00
Account 1150000000 should give me 60.00, because: Accoutn 1150000000=Account 115001000 + Account 1150002000.
Account 1150001000= Account 1150001001 + Account 1150001002 (10.00 + 20.00)=30.00
Account 1150002000= 30.00
Account 1120000000 should have the value of all sub accounts 112000x000, and sub accounts 112001000 should sum al sub accounts 112000100x and so on.
Can someone help me please.
Last edited by nachosv; 11-12-2008 at 12:33 AM.
Hi Nachosv, and welcome to the forum.
I think the following formula, entered into cell H2 and filled downward, will work for you. It will roll up the sums for column G based on the codes in column A. (You have two columns of values, F & G, and I wasn't sure which one you wanted to sum so I picked G.)
After typing or copying/pasting this formula into H2, you must press CTRL+SHIFT+ENTER to confirm the formula, not just ENTER. When done correctly Excel will automatically insert braces, { }, around the formula.
Let me know how this works. It works perfectly on your sample data from the post and seems to work just fine on your sample spreadsheet.
=IF(RIGHT($A2,4)="0000",SUM(IF(LEFT($A$2:$A$30,6)=LEFT($A2,6),IF(RIGHT($A$2:$A$30,1)="0",$G$2:$G$30))),IF(AND(MID($A2,7,1)<>"0",RIGHT($A2,1)="0"),SUM(IF(LEFT($A$2:$A$30,6)=LEFT($A2,6),IF(MID($A$2:$A$30,7,1)=MID($A2,7,1),$G$2:$G$30))),$G2))
Hi PJoaquin, I tried what you mentioned in your reply however it only worked for the first cell, when i tried to copy it as formula downward i get the error message #¿Name?. Is there any chance you could attached the file where you tried, thanks in advanced. By the way, thanks for your quick respone and the welcome to the forum
My mistake, the formula worked, it's just i had to type formulas in spanish, i don't know why but i did that and it worked, however there was a mistake, attached is the file with the cells marked and the explanation is this:
it seems it didn't work properly, as you may si in the cell with yellow background the result is 30, however that is incorrect because if we sum the 2 sub accounts marked with red background,
the result should be 60.00, in this case sub account 1150001000 has 2 sub sub accounts, 1150001001 and 1150001002, and sub account 1150002000 doesn't have sub sub accounts
So the main account is 1150000000 and it should sum all the sub accounts, so the sub accounts should reflect the sum of all sub sub accounts, i hope i explained myself
So there are 3 hierarchys
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks