# Calculating ending balances (sum with several conditions)

#### SinineBlue

##### New Member

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

##### Active Member
Hi, please put an attachment in DropBox, Drive, etc

#### SinineBlue

##### New Member
Hi, please put an attachment in DropBox, Drive, etc
Sorry for the delay and thank you for your interest.

#### jasonb75

##### Well-known Member
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

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

##### Well-known Member
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

##### New Member
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:

1,081,990
Messages
5,362,584
Members
400,683
Latest member
LogChief

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...