Calculating balances across multiple accounts

[muhleebbin]

Could someone a lot smarter than me help me figure out this puzzle of calculating the running balance of our accounts?

So columns A-K are the current columns available on a workbook I have but I'd like to be able to incorporate column L, but cannot seem to figure out the best method to tackle this. Basically i'd like the first instance of an account to take the beginning balance and add/subtract based off the credit/debit. Each subsequent instance of an account number would then look for the last entry and add/subtract from there.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Matched	Account Name	Account Number	As of Date	Data Type	BAI Code Description	Debit	Credit	Customer Reference	Text	Notes	Balance						Account	Beginning balance
2		Test1	1	7/1/2021	Detail Credits	Preauthorized ACH Credit		1.00				101.00						1	100.00
3		Test2	2	7/1/2021	Detail Credits	Preauthorized ACH Credit		2.00				202.00						2	200.00
4		Test2	2	7/1/2021	Detail Credits	Preauthorized ACH Credit		2.00				204.00						3	300.00
5		Test2	2	7/1/2021	Detail Debits	Preauthorized ACH Debit	2.00					202.00						4	400.00
6		Test3	3	7/1/2021	Detail Credits	Preauthorized ACH Credit		3.00				303.00						5	500.00
7		Test3	3	7/1/2021	Detail Debits	Preauthorized ACH Debit	3.00					300.00						6	600.00
8		Test4	4	7/1/2021	Detail Credits	Preauthorized ACH Credit		4.00				404.00
9		Test5	5	7/1/2021	Detail Credits	Preauthorized ACH Credit		5.00				505.00
10		Test5	5	7/1/2021	Detail Debits	Checks Paid Debit	5.00					500.00
11		Test6	6	7/1/2021	Detail Credits	Preauthorized ACH Credit		6.00				606.00
12		Test1	1	7/2/2021	Detail Credits	Preauthorized ACH Credit		1.00				102.00
13		Test1	1	7/2/2021	Detail Debits	Preauthorized ACH Debit	1.00					101.00
14		Test1	1	7/2/2021	Detail Debits	Preauthorized ACH Debit	1.00					100.00
15		Test2	2	7/2/2021	Detail Credits	Preauthorized ACH Credit		2.00				204.00
16		Test2	2	7/2/2021	Detail Debits	Preauthorized ACH Debit	2.00					202.00
17		Test2	2	7/2/2021	Detail Debits	Checks Paid Debit	2.00					200.00
18		Test3	3	7/2/2021	Detail Credits	Preauthorized ACH Credit		3.00				303.00
19		Test3	3	7/2/2021	Detail Debits	Preauthorized ACH Debit	3.00					300.00
20		Test3	3	7/2/2021	Detail Debits	Preauthorized ACH Debit	3.00					297.00
21		Test3	3	7/2/2021	Detail Debits	Preauthorized ACH Debit	3.00					294.00
22		Test3	3	7/2/2021	Detail Debits	Preauthorized ACH Debit	3.00					291.00
23		Test4	4	7/2/2021	Detail Credits	Preauthorized ACH Credit		4.00				408.00
24		Test4	4	7/2/2021	Detail Debits	Preauthorized ACH Debit	4.00					404.00
25		Test4	4	7/2/2021	Detail Debits	Preauthorized ACH Debit	4.00					400.00
26		Test4	4	7/2/2021	Detail Debits	Preauthorized ACH Debit	4.00					396.00
Sheet1

Thanks in advance for your help here!

 

[kennypete]

This will do it. It's not looking at the previous value. Rather it looks up the opening and then subtracts and adds the applicable entries for each account. For more Col L entries it can just be dragged down.

calculating-balances-across-multiple-accounts.1177286.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Matched	Account Name	Account Number	As of Date	Data Type	BAI Code Description	Debit	Credit	Customer Reference	Text	Notes	Balance						Account	Beginning balance
2		Test1	1	7/01/2021	Detail Credits	Preauthorized ACH Credit		1				101						1	100
3		Test2	2	7/01/2021	Detail Credits	Preauthorized ACH Credit		2				202						2	200
4		Test2	2	7/01/2021	Detail Credits	Preauthorized ACH Credit		2				204						3	300
5		Test2	2	7/01/2021	Detail Debits	Preauthorized ACH Debit	2					202						4	400
6		Test3	3	7/01/2021	Detail Credits	Preauthorized ACH Credit		3				303						5	500
7		Test3	3	7/01/2021	Detail Debits	Preauthorized ACH Debit	3					300						6	600
8		Test4	4	7/01/2021	Detail Credits	Preauthorized ACH Credit		4				404
9		Test5	5	7/01/2021	Detail Credits	Preauthorized ACH Credit		5				505
10		Test5	5	7/01/2021	Detail Debits	Checks Paid Debit	5					500
11		Test6	6	7/01/2021	Detail Credits	Preauthorized ACH Credit		6				606
12		Test1	1	7/02/2021	Detail Credits	Preauthorized ACH Credit		1				102
13		Test1	1	7/02/2021	Detail Debits	Preauthorized ACH Debit	1					101
14		Test1	1	7/02/2021	Detail Debits	Preauthorized ACH Debit	1					100
15		Test2	2	7/02/2021	Detail Credits	Preauthorized ACH Credit		2				204
16		Test2	2	7/02/2021	Detail Debits	Preauthorized ACH Debit	2					202
17		Test2	2	7/02/2021	Detail Debits	Checks Paid Debit	2					200
18		Test3	3	7/02/2021	Detail Credits	Preauthorized ACH Credit		3				303
19		Test3	3	7/02/2021	Detail Debits	Preauthorized ACH Debit	3					300
20		Test3	3	7/02/2021	Detail Debits	Preauthorized ACH Debit	3					297
21		Test3	3	7/02/2021	Detail Debits	Preauthorized ACH Debit	3					294
22		Test3	3	7/02/2021	Detail Debits	Preauthorized ACH Debit	3					291
23		Test4	4	7/02/2021	Detail Credits	Preauthorized ACH Credit		4				408
24		Test4	4	7/02/2021	Detail Debits	Preauthorized ACH Debit	4					404
25		Test4	4	7/02/2021	Detail Debits	Preauthorized ACH Debit	4					400
26		Test4	4	7/02/2021	Detail Debits	Preauthorized ACH Debit	4					396
Sheet1

Cell Formulas
Range	Formula
L2:L26	L2	=VLOOKUP(C2,R:S,2,FALSE)-SUMIFS($C$1:$C2,$G$1:$G2,C2)+SUMIFS($C$1:$C2,$H$1:$H2,C2)

 

[muhleebbin]

Kenny,

Thank you soooo much!! I actually was on the right track except I couldn't figure out for the life of me to use C2 as the criteria

It definitely is a case of the Friday's ugghhhh