Calculating ending balances (sum with several conditions)

[SinineBlue]

The example with notes about problem is attached.

Shortly: I want to automatically calculate ending balances from small general ledger I keep in excel.
The formula should understand what account we are speaking of, what month is the transaction from and is the column debit or credit. I hope that the picture helps to understand what I am trying to accomplish.
I have tried with INDEX, MATCH, VLOOKUP & HLOOKUP, SUMIFS... but I have not been able to figure this out.

I would be very thankful for any notes or helpful hints.

 

[hernantorres23]

Hi, please put an attachment in DropBox, Drive, etc

 

[SinineBlue]

Hi, please put an attachment in DropBox, Drive, etc

Sorry for the delay and thank you for your interest.

Drive link is below:

Excel problem

docs.google.com

 

[jasonb75]

The way that you have your sheet arranged is not very formula friendly, this is the best that I can do with your existing layout without using ridiculously long formulas.

Please be aware that I've had to assume basic format will always be the same in order to make this work, i.e. account No. always in row 1, name in row 2, opening balance in row 4, 2 columns for each account with debit amounts on the left.

I've also assumed that the opening balances are debit/negative amounts as they are in the debit column, although the green font does suggest the opposite.

Based on your googledocs example, enter this formula into K9 and fill down to populate the opening balances.

=SUMPRODUCT(($D$1:$F$1=$I9)*($D$2:$F$2=$J9)*($D$4:$F$4-$E$4:$G$4))

Then this one into L9 then copy down and right for the montly balances.

=K9+SUMPRODUCT(($D$1:$F$1=$I9)*($D$2:$F$2=$J9)*($B$5:$B$16=L$7)*($D$5:$F$16-$E$5:$G$16))

Hope this helps.

 

[SinineBlue]

The way that you have your sheet arranged is not very formula friendly, this is the best that I can do with your existing layout without using ridiculously long formulas.

Please be aware that I've had to assume basic format will always be the same in order to make this work, i.e. account No. always in row 1, name in row 2, opening balance in row 4, 2 columns for each account with debit amounts on the left.

I've also assumed that the opening balances are debit/negative amounts as they are in the debit column, although the green font does suggest the opposite.

Based on your googledocs example, enter this formula into K9 and fill down to populate the opening balances.

=SUMPRODUCT(($D$1:$F$1=$I9)*($D$2:$F$2=$J9)*($D$4:$F$4-$E$4:$G$4))

Then this one into L9 then copy down and right for the montly balances.

=K9+SUMPRODUCT(($D$1:$F$1=$I9)*($D$2:$F$2=$J9)*($B$5:$B$16=L$7)*($D$5:$F$16-$E$5:$G$16))

Hope this helps.

It helped alot! Thank you very much!

This is not formula friendly data indeed. I used sample general ledger file. I can now see that I should have thought ahead that at some point I want to start analyzing this data. This is a good note and next time I will not make the same mistake. :D

In accounting you have active (for example bank) and passive (for example debts to suppliers) accounts for balance sheet. In active accounts increase is in debit side and in passive accounts increase is in credit side (dept rises). In my example there are only active accounts. So these debit and credit words in this case do not refer to negative or positive exactly, but after google'ing "debit" I can see why it might have been confusing.

Thanks again!

 

[jasonb75]

Reading your reply and looking at the formula again, I'm not entirely sure if it is correct or not (I thought that the opening balance part was wrong, but second look, it appears that I got it right by getting it wrong)

The formula is evaluating all amounts in the Debit columns as positive and the Credit columns as negative (actually positives to subtract, which equate to the same thing).

I would suggest validiating a random selection of results with manual calculation to avoid any uncertainty.

 

[SinineBlue]

I did end up using a bit different approach that was inspired by jasonb75 formula (Thanks again, could not have done it without your help! ). I will just post it here, maybe it will be useful for someone google'ing the same problem

I added the final product to my google drive link on sheet "Solved FINAL", but just in case that is not permanent enough, I will also attach a picture below.

1.
I made changes to the original data - I added row with account number in debit and credit column with additional "1" for debit and "2" for credit (picture below).

2.
Final formula looks like that: