Calculating ending balances (sum with several conditions)

SinineBlue

New Member
Joined
Nov 28, 2019
Messages
4
Office Version
2016
Platform
Windows
1574970991121.png

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
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
Hi, please put an attachment in DropBox, Drive, etc
 

SinineBlue

New Member
Joined
Nov 28, 2019
Messages
4
Office Version
2016
Platform
Windows
Hi, please put an attachment in DropBox, Drive, etc
Sorry for the delay and thank you for your interest. :)

Drive link is below:
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,150
Office Version
2019
Platform
Windows
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
Joined
Nov 28, 2019
Messages
4
Office Version
2016
Platform
Windows
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
Joined
Dec 30, 2008
Messages
7,150
Office Version
2019
Platform
Windows
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) o_O

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
Joined
Nov 28, 2019
Messages
4
Office Version
2016
Platform
Windows
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).
1577358027909.png

2.
Final formula looks like that:
1577357932055.png
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top