Calculating ending balances (sum with several conditions)

SinineBlue

New Member
Joined
Nov 28, 2019
Messages
4
Office Version
  1. 2016
Platform
  1. 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. :)
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi, please put an attachment in DropBox, Drive, etc

Sorry for the delay and thank you for your interest. :)

Drive link is below:
 
Upvote 0
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.
 
Upvote 0
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! :)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top