Calculate the Total Balance of Each Bank

[4hoteliers]

I have an excel sheet for keeping trace of Credits & Debits of my 4 Banks from January to December. I need a formula that will calculate the balance of 4 banks and need to put it at end of each month or at the end of the year in my excel sheet. Attached a screenshot of the data in my excel file.

 

[Macropod]

It is impossible to tell what your screenshot represents - it's so blurry almost nothing is legible. Neither does it appear to give any indication of what goes where.

 

[4hoteliers]

Is there any other way to upload the screenshot as came out a message that file was too big to upload...

 

[Macropod]

Have you tried the 'Upload Mini-sheet' tool? It's on the posting menu (XL2BB) and there's a help link to it at the bottom of the same screen

 

[4hoteliers]

TRANSACTION BALANCE SHEET.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Account	Date	Payee	Category		PAYMENT	DEPOSIT	Cleared Payment	Cleared Deposit	Account Balance	Cleared Balance	BALANCE	BANK NAME	Each Bank BALANCE
4	Balance EUROBANK	01-Jan-24	[Balance EUROBANK As of 01/01/2024]	[Balance - EUROBANK	c		13,843.53	-	13,843.53	13,843.53	13,843.53	13,843.53	EUROBANK	13,843.53
5	Balance PIRAEUS	01-Jan-24	[Balance EUROBANK As of 01/01/2024]	[Balance - PIRAEUS	c		388.50	-	388.50	388.50	388.50	14,232.03	PIRAEUS	388.50
6	Balance NATIONAL	01-Jan-24	[Balance EUROBANK As of 01/01/2024]	[Balance - NATIONAL	c		38,369.03	-	38,369.03	38,369.03	38,369.03	52,601.06	NATIONAL	38,369.03
7	Cash	01-Jan-24	[Balance EUROBANK As of 01/01/2024]	[Balance - CASH BALANCE	c		5,114.08	-	5,114.08	5,114.08	5,114.08	57,715.14	CASH BALANCE	5,114.08
8	Web Banking	05-Jan-24	CHANNEL MANAGER VILLA PAYMENT	CALDERA VILLA CHANNEL MANAGER - NATIONAL	c	496.00		496.00	-	(496.00)	(496.00)	57,219.14	NATIONAL	37,873.03
9	Web Banking	05-Jan-24	VIASUN ADVNACE GROUP DEPOSIT	INCOME OTHER - NATIONAL	c		608.40	-	608.40	112.40	112.40	57,827.54	NATIONAL	38,481.43
10	Web Banking	25-Jan-24	ΝΕΑ ΡΥΘΜΙΣΗ 2022 ΔΟΣΗ 15 ΔΕΚΕΜΒΡΙΟΣ 2024	ΠΛΗΡΩΜΕΣ ΔΟΣΕΩΝ ΔΙΑΚΑΝΟΝΙΣΜΩΝ - NATIONAL	c	2,749.99		2,749.99	-	(2,637.59)	(2,637.59)	55,077.55	NATIONAL	35,731.44
11			February-24		c			-	-	-	-	55,077.55		-
12	Web Banking	17-Feb-24	TRAVELWAY GROUP DEPOSIT	INCOME OTHER - NATIONAL	c		421.00	-	421.00	(2,216.59)	(2,216.59)	55,498.55	NATIONAL	36,152.44
13	Transfer to NATIONAL	19-Feb-24	BLOOM TRAVEL GROUPS DEPOSIT 30% FOR ALL 4 GROUPS	INCOME OTHER - NATIONAL	c		2,433.60	-	2,433.60	2,433.60	2,433.60	57,932.15	NATIONAL	38,586.04
14	Web Banking	26-Feb-24	ΡΥΘΜΙΣΗ ΕΦΟΡΙΑΣ 2022 CALDERA VIEW OE ΚΑΙ ΦΠΑ 5Η ΔΟΣΗ	ΠΛΗΡΩΜΕΣ ΔΟΣΕΩΝ ΔΙΑΚΑΝΟΝΙΣΜΩΝ - NATIONAL	c	5,017.06		5,017.06	-	(7,233.65)	(7,233.65)	52,915.09	NATIONAL	33,568.98
15	Web Banking	28-Feb-24	GNOMO ORZO GROUP DEPOSIT	INCOME OTHER - NATIONAL	c		2,500.00	-	2,500.00	(4,733.65)	(4,733.65)	55,415.09	NATIONAL	36,068.98
16	Web Banking	28-Feb-24	ΣΙΓΑΛΑ ΑΝΤΙΓΟΝΗ ΕΝΑΝΤΙ ΜΕΡΙΣΜΑΤΩΝ 400 Γ	ΑΝΤΙΓΟΝΗ ΣΙΓΑΛΑ - NATIONAL	c	400.00		400.00	-	(5,133.65)	(5,133.65)	55,015.09	NATIONAL	35,668.98
17			March-24		c			-	-	-	-	55,015.09		-
18	Web Banking	03-Mar-24	ΜΕΤΑΦΟΡΑ ΣΕ ΠΑΝΑΓΙΩΤΗ ΓΙΑ ΜΕΤΑΦΟΡΑ ΣΕ ΔΗΜΗΤΡΗ ΑΛΒΑΝΟ ΓΙΑ ΦΥΤΟΦΑΡΜΑΚΑ ΜΕ ΤΙΜ	ΓΕΩΠΟΝΟΣ - NATIONAL	c	80.00		80.00	-	(5,213.65)	(5,213.65)	54,935.09	NATIONAL	35,588.98
19	Web Banking	04-Mar-24	GNOMO ORZO DEPOSIT 2	INCOME OTHER - NATIONAL	c		1,500.00	-	1,500.00	(3,713.65)	(3,713.65)	56,435.09	NATIONAL	37,088.98
20	Web Banking	04-Mar-24	HOTELBEDS DEPOSIT	HOTELBEDS - NATIONAL	c		5,000.00	-	5,000.00	1,286.35	1,286.35	61,435.09	NATIONAL	42,088.98
21	Web Banking	06-Mar-24	GNOMO ORZO DEPOSIT 3	INCOME OTHER - NATIONAL	c		1,325.11	-	1,325.11	2,611.46	2,611.46	62,760.20	NATIONAL	43,414.09
22	Cash	13-Mar-24	ΣΙΓΑΛΑΣ ΜΗΝΑΣ 300 ΜΕΤΡΗΤΑ	ΔΙΑΦΟΡΑ ΕΞΟΔΑ ΜΕΤΡΗΤΑ DEMO - CASH BALANCE	c	300.00		300.00	-	4,814.08	4,814.08	62,460.20	CASH BALANCE	4,814.08
23			April-24		c			-	-	-	-	62,460.20		-
24	C-card NATIONAL	08-Apr-24	BOOKING COM NON REFUNDABLE	POS RECEPTION - NATIONAL	c		3,919.02	-	3,919.02	3,919.02	3,919.02	66,379.22	NATIONAL	47,333.11
25	Cash	16-Apr-24	ΜΕΤΑΦΟΡΑ ΑΠΌ EUROBANK ΣΕ ΤΑΜΕΙΟ	Z RECEPTION CASH - CASH BALANCE	c		5,000.00	-	5,000.00	9,814.08	9,814.08	71,379.22	CASH BALANCE	9,814.08
Sheet1

Cell Formulas
Range	Formula
D4,D6	D4	='/Users/michalispousios/Desktop/[xxx17SEP24 - FIXED - CVR Cash Flow (Money Manager) to check 2.xlsx]Categories'!A2
H4:H25	H4	=IF(OR(E4="c",E4="R"),F4,0)
I4:I25	I4	=IF(OR(E4="c",E4="R"),G4,0)
J4:J25	J4	=SUMIF(A$3:A4,"="&A4,G$3:G4)-SUMIF(A$3:A4,"="&A4,F$3:F4)
K4:K25	K4	=SUMIF(A$3:A4,"="&A4,I$3:I4)-SUMIF(A$3:A4,"="&A4,H$3:H4)
L4:L25	L4	=IF(ISERROR(OFFSET(L4,-1,0,1,1)+G4-F4),G4-F4,OFFSET(L4,-1,0,1,1)+G4-F4)
M4:M25	M4	=IFERROR(RIGHT(D4,LEN(D4)-SEARCH("-",D4)),"")
N4:N25	N4	=SUMIF(M$3:M4,M4,G$3:G4)-SUMIF(M$3:M4,M4,F$3:F4)
G5	G5	=800-411.5
C4:C7	C4	="[Balance EUROBANK As of "&TEXT(B4,"dd/mm/yyyy")&"]"

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D7,D11,D17,D23,D29,D35	Expression	=AND(NOT(ISBLANK(D4));ISERROR(MATCH(D4;categories;0)))	text	YES
D4:D7,D11,D17,D23,D29,D35	Expression	=OR(D4="[Balance]";D4="[Transfer]";ISBLANK(D4))	text	YES
D4:D7,D11,D17,D23,D29,D35	Expression	=OR(ISERROR(MATCH(D4;yearlyA;0));ISERROR(MATCH(D4;monthlyA;0)))	text	YES
A4:A71	Expression	=AND(ISERROR(MATCH(A4;accounts;0));NOT(ISBLANK(A4)))	text	YES
L4:N73	Cell Value	<0	text	YES

Cells with Data Validation
Cell	Allow	Criteria
A3:A25	List	=accounts

 

[Peter_SSs]

@4hoteliers

A few house-keeping issues:

I have merged your two threads per #12 of the Forum Rules

Also, your thread title mentions that you are using Microsoft 365. I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have removed the 'Mark as solution' tick from post #4 as it clearly is not a solution to the thread question. That option is not to mark a thread as 'Solved' or as 'I don't need any more help in the thread' but to mark an actual solution to help future readers. There is a link for more information about that in my signature block below if you need it.

 

[4hoteliers]

@4hoteliers

A few house-keeping issues:

I have merged your two threads per #12 of the Forum Rules

Also, your thread title mentions that you are using Microsoft 365. I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have removed the 'Mark as solution' tick from post #4 as it clearly is not a solution to the thread question. That option is not to mark a thread as 'Solved' or as 'I don't need any more help in the thread' but to mark an actual solution to help future readers. There is a link for more information about that in my signature block below if you need it.

Thank you very much for your help and your suggestions. Have updated my account details.

 

[4hoteliers]

Can someone help me with a formula to calculate the total balance of 4 banks???