Need balance of each bank

[4hoteliers]

Balance.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Account	Payee	Category	reconcile or clear	Payment	Deposit	Cleared Payment	Cleared Deposit	Account Balance	Cleared Balance	Balance	BANK NAME	EACH BANK balance
2	Balance EUROBANK	Balance - EUROBANK	[Balance - EUROBANK	c		15000	0	15000	15,000.00	15,000.00	15,000.00	EUROBANK
3	Balance ALPHA BAK	Balance - ALPHA BANK	[Balance - ALPHA BANK	c		8500	0	8500	8,500.00	8,500.00	23,500.00	ALPHA BANK
4	Cash	Interest	INTEREST - EUROBANK	c		0.12	0	0.12	0.12	0.12	23,500.12	EUROBANK
5	Cash	Inteest Tax	INTEREST TAX - EUROBANK	c	0.02		0.02	0	0.10	0.10	23,500.10	EUROBANK
6	Web Banking	Vodafone	Phone VODAFONE - EUROBANK	c	21.61		21.61	0	(21.61)	(21.61)	23,478.49	EUROBANK
7	Web Banking	Shared Utility Monthly Bill	Shared Utility Monthly Bill - ALPHA	c	39.33		39.33	0	(60.94)	(60.94)	23,439.16	ALPHA
8	Web Banking	Office funriture Buy	Office Furniture Buy - EUROBANK	c	73.75		73.75	0	(134.69)	(134.69)	23,365.41	EUROBANK
9	Web Banking	Software Buy	Software Buy - ALPHA	c	104.13		104.13	0	(238.82)	(238.82)	23,261.28	ALPHA
10	Web Banking	Car Leasing	Car Leasing - EUROBANK	c	447.64		447.64	0	(686.46)	(686.46)	22,813.64	EUROBANK
11	Client Deposit	Client 1 Deposit	THIRA'S DOLPHIN - EUROBANK	c		1000	0	1000	1,000.00	1,000.00	23,813.64	EUROBANK
12	Cash	Withraw cash	Withdraw - Eurobank	c	400		400	0	(399.90)	(399.90)	23,413.64	Eurobank
13	Cash	Withraw cash	Withdraw - ALPHA	c	200		200	0	(599.90)	(599.90)	23,213.64	ALPHA
14	Web Banking	Acount Payment	ACCOUNTANT PAYMENT - EUROBANK	c	286.44		286.44	0	(972.90)	(972.90)	22,927.20	EUROBANK
15	Web Banking	Annual Advertisitng	Annual Advertising Hotelier Academy - EUROBANK	c	3300		3300	0	(4,272.90)	(4,272.90)	19,627.20	EUROBANK
16	Cash	Withraw cash	Withdraw - ALPHA	c	200		200	0	(799.90)	(799.90)	19,427.20	ALPHA
17	Web Banking	Professional chamber payment	Professional Chamber - EUROBANK	c	25		25	0	(4,297.90)	(4,297.90)	19,402.20	EUROBANK
18	Web Banking	Electricity bill payment	Electricity - EUROBANK	c	420.5		420.5	0	(4,718.40)	(4,718.40)	18,981.70	EUROBANK
Sheet1

Cell Formulas
Range	Formula
G2:G18	G2	=IF(OR(D2="c",D2="R"),E2,0)
H2:H18	H2	=IF(OR(D2="c",D2="R"),F2,0)
I2:I18	I2	=SUMIF(A$1:A2,"="&A2,F$1:F2)-SUMIF(A$1:A2,"="&A2,E$1:E2)
J2:J18	J2	=SUMIF(A$1:A2,"="&A2,H$1:H2)-SUMIF(A$1:A2,"="&A2,G$1:G2)
K2:K18	K2	=IF(ISERROR(OFFSET(K2,-1,0,1)+F2-E20),F2-E2,OFFSET(K2,-1,0,1)+F2-E2)
L2:L18	L2	=RIGHT(C2,LEN(C2)-SEARCH("-",C2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D18	Expression	=AND(NOT(ISBLANK(C2));ISERROR(MATCH(C2;categories;0)))	text	YES
C2:D18	Expression	=OR(C2="[Balance]";C2="[Transfer]";ISBLANK(C2))	text	YES
C2:D18	Expression	=OR(ISERROR(MATCH(C2;yearlyA;0));ISERROR(MATCH(C2;monthlyA;0)))	text	YES

Need to have in column M the balance for each bank. What kind of formula should I use?

 

[Sufiyan97]

Is it what you want?

Book4
	B	C	D	E	F	G	H	I	J	K	L	M
1	Payee	Category	reconcile or clear	Payment	Deposit	Cleared Payment	Cleared Deposit	Account Balance	Cleared Balance	Balance	BANK NAME	EACH BANK balance
2	Balance - EUROBANK	[Balance - EUROBANK	c		15000	0	15000	15000	15000	15000	EUROBANK	259823.3
3	Balance - ALPHA BANK	[Balance - ALPHA BANK	c		8500	0	8500	8500	8500	23500	ALPHA BANK	23500
4	Interest	INTEREST - EUROBANK	c		0.12	0	0.12	0.12	0.12	23500.12	EUROBANK	259823.3
5	Inteest Tax	INTEREST TAX - EUROBANK	c	0.02		0.02	0	0.1	0.1	23500.1	EUROBANK	259823.3
6	Vodafone	Phone VODAFONE - EUROBANK	c	21.61		21.61	0	-21.61	-21.61	23478.49	EUROBANK	259823.3
7	Shared Utility Monthly Bill	Shared Utility Monthly Bill - ALPHA	c	39.33		39.33	0	-60.94	-60.94	23439.16	ALPHA	89341.28
8	Office funriture Buy	Office Furniture Buy - EUROBANK	c	73.75		73.75	0	-134.69	-134.69	23365.41	EUROBANK	259823.3
9	Software Buy	Software Buy - ALPHA	c	104.13		104.13	0	-238.82	-238.82	23261.28	ALPHA	89341.28
10	Car Leasing	Car Leasing - EUROBANK	c	447.64		447.64	0	-686.46	-686.46	22813.64	EUROBANK	259823.3
11	Client 1 Deposit	THIRA'S DOLPHIN - EUROBANK	c		1000	0	1000	1000	1000	23813.64	EUROBANK	259823.3
12	Withraw cash	Withdraw - Eurobank	c	400		400	0	-399.9	-399.9	23413.64	Eurobank	259823.3
13	Withraw cash	Withdraw - ALPHA	c	200		200	0	-599.9	-599.9	23213.64	ALPHA	89341.28
14	Acount Payment	ACCOUNTANT PAYMENT - EUROBANK	c	286.44		286.44	0	-972.9	-972.9	22927.2	EUROBANK	259823.3
15	Annual Advertisitng	Annual Advertising Hotelier Academy - EUROBANK	c	3300		3300	0	-4272.9	-4272.9	19627.2	EUROBANK	259823.3
16	Withraw cash	Withdraw - ALPHA	c	200		200	0	-799.9	-799.9	19427.2	ALPHA	89341.28
17	Professional chamber payment	Professional Chamber - EUROBANK	c	25		25	0	-4297.9	-4297.9	19402.2	EUROBANK	259823.3
18	Electricity bill payment	Electricity - EUROBANK	c	420.5		420.5	0	-4718.4	-4718.4	18981.7	EUROBANK	259823.3
Sheet1

Cell Formulas
Range	Formula
G2:G18	G2	=IF(OR(D2="c",D2="R"),E2,0)
H2:H18	H2	=IF(OR(D2="c",D2="R"),F2,0)
I2:I18	I2	=SUMIF(A$1:A2,"="&A2,F$1:F2)-SUMIF(A$1:A2,"="&A2,E$1:E2)
J2:J18	J2	=SUMIF(A$1:A2,"="&A2,H$1:H2)-SUMIF(A$1:A2,"="&A2,G$1:G2)
K2:K18	K2	=IF(ISERROR(OFFSET(K2,-1,0,1)+F2-E20),F2-E2,OFFSET(K2,-1,0,1)+F2-E2)
L2:L18	L2	=RIGHT(C2,LEN(C2)-SEARCH("-",C2))
M2:M18	M2	=SUMIF(L:L,L2,K:K)

 

[Habtest]

I think you are looking for the balance by bank at the end of each transaction, in which case put below formula in M2 and drag on

=SUMIF(L$1:L2,L2,F$1:F2)-SUMIF(L$1:L2,L2,E$1:E2)

Also, you may want to check the column K formula, there could be a typo in cell reference though it's not skewing the result.

 

[4hoteliers]

Habtest, thank you for your reply. Your formula works fine only for the first bank (Eurobank). It doesn't work with the second bank (APLHA BANK) since it is not subtracting the payments from the initial amount (8500). Any suggestion on how to fix this?

 

[Sufiyan97]

Habtest, thank you for your reply. Your formula works fine only for the first bank (Eurobank). It doesn't work with the second bank (APLHA BANK) since it is not subtracting the payments from the initial amount (8500). Any suggestion on how to fix this?

Is

ALPHA BANK

and

ALPHA

Same?
if it is then you need to fix it.

 

[Sufiyan97]

Is

ALPHA BANK

and

ALPHA

Same?
if it is then you need to fix it.

Ignor

 

[Habtest]

Habtest, thank you for your reply. Your formula works fine only for the first bank (Eurobank). It doesn't work with the second bank (APLHA BANK) since it is not subtracting the payments from the initial amount (8500). Any suggestion on how to fix this?

Hi 4hoteliers,

As you can see the issue is more with column L not picking up bank names correctly, and ultimately it's column C not displaying bank names consistently.

The easiest way is to directly change C3 from "[Balance - ALPHA BANK" to "[Balance - ALPHA", or L3 to "ALPHA". If that's not feasible you need to specify on how bank names look like in your data pool.

 

[4hoteliers]

Hadtest, I made a mistake...the bank should be ALPHA (not ALPHA BANK). I will correct it. You think that if I correct the name ALPHA in the cells, your formula will work???

 

[Habtest]

Hadtest, I made a mistake...the bank should be ALPHA (not ALPHA BANK). I will correct it. You think that if I correct the name ALPHA in the cells, your formula will work???

I think so, let me know otherwise.

 

[4hoteliers]

Habtest hello, yes it is working fine!!! Thank you very much for your kind help!!!